Date Formatting in Postgres: A Comprehensive Guide
Tags: date, postgres
Published: 2024-01-22 08:00:00

**PostgreSQL is a powerful relational database management system (RDBMS) that provides a platform for storing, managing, and analyzing structured data and supports various date formatting options. In this guide, we'll focus on how to format dates in a way that is both readable and understandable for users in PostgreSQL.**

---
**Tools used in this tutorial**
[DbVisualizer](https://www.dbvis.com/download/), top rated database management tool and SQL client.
The [PostgreSQL](https://www.postgresql.org/) database

---

## Understanding Date and Time Data Types in PostgreSQL
PostgreSQL supports various date and time data types. Each data type has its own set of options that can be used to format date and time in a custom-tailored format. Some of these data types include `DATE`, `TIME`, `TIMESTAMP`, `TIMESTAMP WITH TIME ZONE`, and `INTERVAL`.

- The `DATE` data type stores only the date information. 
- The `TIME` data type represents a specific time of the day.
-  `TIMESTAMP` stores both date and time information.
- The `TIMESTAMP WITH TIME ZONE` data type is similar to `TIMESTAMP` but also includes the time zone.
- The `INTERVAL` data type represents a time interval.

Now that we have a basic understanding of the data types, let's learn how to format dates in PostgreSQL.

## Date Formatting Functions in PostgreSQL
In this section, we’re going to dive into the various date formatting functions in Postgres. Let’s look at the most commonly used functions that are used to format date and time data in PostgreSQL:

- `to_char()`: converts a date or timestamp value to a string value in a specified format.
- `to_timestamp()`: converts a string value to a timestamp value.
- to_date(): this converts a string value to a date value.
Great. We have touched on some of the standard date formatting functions in PostgreSQL, let us now look at how to implement these functions.

## Using the to_char() Function
The `to_char()` function is used to convert a date or a timestamp value to a string value in a specified format in PostgreSQL. To return a timestamp value in the YYYY-MM-DD format, use the following query:

```
SELECT
 to_char(current_timestamp, 
'YYYY-MM-DD HH24:MI:SS'
);
```

This will return the current timestamp in the format **YYYY-MM-DD HH24 :MI :SS**, where **YYYY** represents the four-digit year, **MM** represents the two-digit month, **DD**, the two-digit day of the month, while **HH24**, **MI**, and **SS**, represent the the two-digit hour of the day in 24-hour format, the two-digit minute of the hour, and the two-digit second of the minute respectively.

## Using the to_timestamp Function
The `to_timestamp` function converts a string value to a timestamp value in PostgreSQL. This function takes two arguments: the input string and the format string. The function of the format string is to define the order and format of the date and time elements in the input string.

Let us build a query that will convert a string value to a timestamp and then extract the year from the timestamp:

```
SELECT EXTRACT(year FROM to_timestamp('2023-07-06 11:05:00', 
'YYYY-MM-DD HH24:MI:SS'
));
```

In this case, the provided timestamp is **2023-07-06 11:05:00**. The EXTRACT() function is used to extract the year component from it and the to_timestamp() function is used to convert the given string into a timestamp format. This query would therefore return the year 2023.

## Using the to_date() Function
The `to_date()` function is used to convert a string value to a date value in PostgreSQL and also takes both the input and format string arguments. 

Let us build a query that will convert a string value to a date value:

```
SELECT to_date('2024-01-06', 'YYYY-MM-DD');
```

This query will convert the input string '2024-01-06' to a date value in PostgreSQL.

We have seen that the format string used in `to_timestamp()` and `to_date()` is similar to the format string used in `to_char()`. The format string is responsible for defining the order and format of the date and time components in the input string.

Having gained a foundational grasp of the date and time formatting functions that PostgreSQL accommodates, let's now explore the techniques for effectively styling and arranging dates in PostgreSQL.

## Personalizing Date and Time Formatting in PostgreSQL 
PostgreSQL also grants users the ability to create custom-tailored formats for dates and times. This is done with the help of the `to_char` function in PostgreSQL. The `to_char()` function takes two arguments: the date or timestamp value to format, and a string that specifies the desired output format.

Let us look at an example of creating a custom date format using the `to_char()` function. 

Let's say you want to display the current date in the format ‘11/01/23’. You can build a query that will help you achieve this by using a custom pattern specifier as shown below:

```
SELECT to_char(current_date, 'DD/MM/YY');
```

The result will be  **11/01/23**.

In the same vein, if you had wanted to display the current date in the format DD/MM/YYYY you could alter the code to build that result using the query below:

```
SELECT to_char(current_date, 'DD/MM/YY');
```

The result here will be **11/01/2023**.

## Formatting Time Zones in PostgreSQL
PostgreSQL offers various built-in functions for formatting time zones, including `AT TIME ZONE`, `timezone()`, and `extract()` which allow you to convert between different time zones and display the time zone offset in a readable format.

## Using the AT TIME ZONE function
For example, here is how to use the `AT TIME ZONE` function to convert a timestamp value to a different time zone: 

```
SELECT current_timestamp AT TIME ZONE 'UTC';
```

This query will return the current timestamp converted to the UTC time zone.

## Using the timezone() Function
The `timezone()` function can be used to convert a timestamp value to a different time zone. Here's an example of how to use this function to convert a timestamp value to the Central Eastern Time (CET) zone:

```
SELECT timezone('CET', current_timestamp);
```

This query will return the current timestamp converted to the CET time zone. 

Also, if you want to display the current timestamp value in say CET, in a specified format, you can use the `to_char()` function in combination with the `timezone()` function to format the timestamp as desired. Here’s how to achieve that:

```
SELECT to_char(timezone('CET', current_timestamp), 'YY-MM-DD HH24:MI:SS');
```

This query will return the current timestamp converted to the CET time zone and formatted as **YYYY-MM-DD HH24: MI: SS**.

## Date Arithmetic and Interval Formatting
Postgres allows users to perform arithmetic operations on dates and intervals by providing the possibility of the addition or subtraction of intervals from dates and format intervals using the intervaldata type and the to_char() function.

This is how you can add an interval to a date using the + operator:

```
SELECT CURRENT_DATE + INTERVAL '1 month';
```

This query will add a month to the current date.

To format the intervals of dates, we can use the **day**, **hour**, **minute**, and **second** placeholders to display the number of days, hours, minutes, and seconds, respectively. Let’s look at an example here:

```
SELECT TO_CHAR(INTERVAL '3 days 2 hours', 'DD days HH hours');
```

This query will return **03 days 02 hours**.

## Date Formatting Use Cases
If you’re a newbie in the database development space, you might be wondering how vital date formatting is in databases. Date formatting in PostgreSQL finds empirical applications in various real-world cases. Let's examine some use cases and see how date formatting can be used effectively:

### (A)Processing Data with Custom Date Formats
Processing of data is a key area where date formatting plays an important role. Let's say you are to produce a monthly sales report that includes the total sales amount for each day of the month. To improve readability, you may want to format the dates in a specific format like "YYYY-MM-DD". 

Let’s look at the example below:

```
SELECT date_trunc('day', order_date) AS sales_date, SUM(amount) AS total_sales
FROM sales
GROUP BY date_trunc('day', order_date)
ORDER BY sales_date;
```

Here, the `date_trunc()` function truncates the `order_date` column to the day level and groups the `sales` data by day. The result will include the `sales` date formatted as `YYYY-MM-DD` and the corresponding `total sales` amount for each day.

### (B)Dates Formatting for Display in User Interfaces
When building user interfaces, it is essential to present dates in an easily understandable format. Postgres allows users to customize the display of dates according to their preferences.

For example, let's consider a web app that displays blog posts. The application fetches the publication date of each blog from the database and has to format it appropriately for display.

```
SELECT title, TO_CHAR(publication_date, 'Mon DD, YYYY') 
AS formatted_date
FROM blog_posts;
```

In the query above, the `TO_CHAR()` function is used to format the `publication_date` column in the desired format, such as "Mon DD, YYYY". The result set will include the blog post title along with the formatted publication date.

## Conclusion
In this guide, we have explored various methods for formatting data in PostgreSQL. By using functions such as the TO_CHAR() and TO_DATE() functions, you can manipulate datetime values to suit your needs. We've looked at how to employ these formatting functions in real-world cases. These techniques provide you with the flexibility to handle date and time values in various formats and situations, making your data more accessible and easier to work with. Now that you're equipped with this knowledge, you can efficiently manage date and time information in your PostgreSQL database. 

[Follow our blog](https://www.dbvis.com/thetable/) for more news about the database space.

## FAQ

### How can you format time zones in PostgreSQL?
In PostgreSQL, you can format time zones using built-in functions such as `AT TIME ZONE`, `timezone()`, and `extract()`. These functions allow you to convert between different time zones and display the time zone offset in a human-readable format.

### What is the difference between the to_timestamp() and to_date() functions in PostgreSQL?
The `to_timestamp()` function is used to convert a string value to a timestamp value in PostgreSQL, while the `to_date()` function is used to convert a string value to a date value. The functions take two arguments: the input string argument, and a string that specifies the format of the input string.

### How do you get the current date and time in PostgreSQL?
To get the current date and time in PostgreSQL, you can use the `SELECT CURRENT_TIMESTAMP;` function or the `SELECT NOW();` statement. Both functions return a timestamp with a time zone value representing the current date and time.


## About the author
Leslie S. Gyamfi is a mobile/web app developer with a passion for creating innovative solutions. He is dedicated to delivering high-quality products and technical articles. You can connect with him on LinkedIn