Date Formatting in Postgres: A Comprehensive Guide

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 - top rated database management tool and SQL client.

The PostgreSQL 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:

1    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:

1    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:

1    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:

1    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:

1    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:

1    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:

1    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:

1    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:

1    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:

1    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:

1    SELECT date_trunc('day', order_date) AS sales_date, SUM(amount) AS total_sales
2    FROM sales3GROUP BY date_trunc('day', order_date)
4    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.

1    SELECT title, TO_CHAR(publication_date, 'Mon DD, YYYY') AS formatted_date
2    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 for more news about the database space.

FAQs

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