PostgreSQL supports various built-in functions to deal with the timestamp values efficiently, such as CURRENT_TIMESTAMP, TO_TIMESTAMP(), DATE_PART(), etc. The TO_CHAR() is one of the data type formatting functions that assist us in converting/formatting the data from one type to another.
This post demonstrates how to utilize the TO_CHAR() function to format a timestamp in Postgres.
How Do I Format a TIMESTAMP in Postgres?
To format a timestamp, specify a timestamp and a valid format as arguments to the TO_CHAR() function:
TO_CHAR(TIMESTAMP, format);
The return type of the stated function is “TEXT”. Visit the official Postgres documentation to see the valid formats for the TO_CHAR() function.
Example 1: Formatting a Timestamp in Postgres
The following example explains how to format a timestamp in Postgres via the TO_CHAR() function:
SELECT TO_CHAR(TIMESTAMP '2001-01-01 5:55:55', 'YYYY/MM/DD HH:MI:SS');
The given timestamp has been successfully formatted to a specific format.
Example 2: Formatting Current Timestamp in Postgres
To format the current timestamp, specify the CURRENT_TIMESTAMP and a particular format to the TO_CHAR() function:
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY/MM/DD HH:MI:SS');
The current timestamp has been successfully converted to the specified format.
Example 3: Formatting Current Timestamp into 'DAY, DD MONTH YYYY HH:MM:SS' Format
In the following snippet, the current timestamp is formatted into the “DAY, DD MONTH YYYY HH:MM:SS” format:
SELECT to_char(CURRENT_TIMESTAMP, 'DAY, DD MONTH YYYY HH:MM:SS');
The current timestamp has been successfully formatted into the specified format.
Example 4: Formatting Current Timestamp to a Date in Postgres
Use the TO_CHAR() function with the “::” operator followed by the “DATE” data type to format a given timestamp to a date:
SELECT TO_CHAR(CURRENT_TIMESTAMP:: DATE, 'MON DD, YYYY');
The current timestamp has been successfully formatted into the given format.
Example 5: Formatting Current Timestamp to Current Time in Postgres
In the below snippet, the TO_CHAR() function is utilized to format the current timestamp into the current time:
SELECT TO_CHAR(CURRENT_TIMESTAMP:: TIME, 'HH:MM:SS');
The current timestamp has been successfully converted into the current time.
That’s all from this Postgres post.
Conclusion
To format a timestamp, specify a timestamp and a valid format as arguments to the Postgres TO_CHAR() function. To format the current timestamp, specify the CURRENT_TIMESTAMP and a particular format to the TO_CHAR() function. Use the TO_CHAR() function with the “::” operator followed by the “DATE” data type to format the given timestamp to date. This write-up presented a detailed guide on getting a timestamp into a specific format using the Postgres TO_CHAR() function.
