PostgreSQL Convert Timestamp to Date

December 6, 2022 | by Arround The Web | No comments

PostgreSQL Convert Timestamp to Date

PostgreSQL is a relational database that has different functions for handling dates. You can work with timestamps or convert the timestamps to dates using different ways. Multiple date functions come with PostgreSQL including converting the string to date, timestamp to date, getting the current date, etc.

This guide focuses on five ways of converting the timestamps to date in PostgreSQL. Let’s dig in!

Converting Timestamp to Date

Timestamp refers to the date and time of a given event. It could be the current date and time or other random date and time that is specified in the database or as input. When you have a timestamp, converting it to date lets you work with the dates more precisely, especially for data analysis. For instance, you may want to extract the day or year from the timestamp. Let’s see how to achieve that.

1. Using the DATE Function

Suppose you have a relation in your PostgreSQL which contains a timestamp such as a given transaction. You can use the DATE() to convert the timestamp to date and extract the value using the following syntax:

# SELECT DATE(timestamp_column) FROM relation_name;

Here’s an example to get the date from the timestamp column in a given table:

2. Using the NOW Function

The now() in PostgreSQL retrieves the current date and time. PostgreSQL has three data types: date, time, and timestamp. You can extract any of the data types using NOW().

To extract the date from the timestamp in PostgreSQL, you can use the NOW() function and specify the date data type using the SELECT keyword.

# SELECT NOW()::date;

Running the command gives the current system date.

3. Using the TO_CHAR Function

The TO_CHAR() function converts various data types. We can use it to convert a timestamp to a date. It uses the following syntax:

# TO_CHAR(expression, format);

The expression represents the data types, such as timestamps, which should be converted to the required strings. There are different ways of using the TO_CHAR() function to extract the date.
First, let’s combine the function with NOW() to get the timestamp and convert it to a date using a specified format. Combine the NOW() function with the date keyword.

# SELECT TO_CHAR(NOW()::DATE, ‘dd-mm-yyyy’);

The “dd-mm-yyyy” represents the date format that we want to get as output.

You can also extract a single detail such as the year. Here’s an example:

Another way of converting the timestamp to date using the TO_CHAR() function is to use the current_timestamp keyword to get the current timestamp, then specify which information to extract from it.

Let’s extract the day of the week, day of the month (DD), and the hour of the day using the 12-hour format (HH12), the minutes (MI), and the seconds (SS) with the following command:

# SELECT TO_CHAR(current_timestamp, ‘Day, DD HH12:MI:SS’);

The TO_CHAR() function has different formats that you can use. The given example is how to use it to convert a timestamp to a date.

4. Using the EXTRACT Function

The EXTRACT() function extracts subfields, such as the year, from the timestamp. The function is handy when only a given date subfield is required. It uses the following syntax:

# EXTRACT(subfield FROM source);

For this example, we extract the month from the specified timestamp using the SELECT keyword and save it as “month.” You can use any name to save it.

# SELECT EXTRACT(MONTH FROM TIMESTAMP ‘2022-11-28 21:34:45’) as month;

You can also extract the day of the month.

The EXTRACT() function can also work with the NOW() function to use the current DateTime as the timestamp instead of specifying it. Here, replace the timestamp with NOW() as follows:

# SELECT EXTRACT(DAY FROM NOW()) as day;

We still get the same output.

You can replace the subfield to extract any information from the timestamp.

5. Using the DATE_PART Function

The DATE_PART() function works similarly to the EXTRACT() function that it extracts the subfield from the timestamp.

Use the following syntax:

# DATE_PART(subfield, source);

The subfield is the year, month, or day that you want to extract. While the source is the specified or current timestamp.

Let’s extract the year from a specified timestamp:

# SELECT DATE_PART(‘year’, TIMESTAMP ‘2012-02-11 12:23:21’) the_year;

To extract the year from the current system’s timestamp, use NOW().

# SELECT DATE_PART(‘year’, NOW()) the_year;

To extract more than one subfield, you can execute this command:

# SELECT date_part(‘year’, TIMESTAMP ‘2022-10-28 10:20:34’) t_year, date_part(‘month’, TIMESTAMP ‘2022-10-28 10:20:34’) t_month, date_part(‘day’, TIMESTAMP ‘2022-10-28 10:20:34’) t_day;

Alternatively, you can run the same command using the NOW() function for the current DateTime:

# SELECT date_part(‘year’, NOW()) t_year, date_part(‘month’, NOW()) t_month, date_part(‘day’, NOW()) t_day;

Conclusion

PostgreSQL offers different functions for working with dates. This guide presented five functions that you can use to convert the timestamps to date including DATE(), TO_CHAR(), NOW(), EXTRACT(), and DATE_PART(). Depending on your goal, you can use any of them to get the date.

Source: linuxhint.com