PostgreSQL allows us to convert a date, interval, number, timestamp, etc., to a string via the TO_CHAR() function. The TO_CHAR() function utilizes a format mask to convert the input value to a string. The format mask must be a valid number or date.

This write-up will teach you how to use the TO_CHAR() function to convert a timestamp into a string in Postgres. So, let’s start!

How Do I Convert a TIMESTAMP to a String in Postgres?

In Postgres, a built-in conversion function named TO_CHAR() is used to convert the given timestamp to a string. To do that, the TO_CHAR() function takes two arguments: a timestamp and a format string specifying how the timestamp should be formatted as a string:

TO_CHAR(timestamp_expression, formatMask);

In place of the “timestamp_expression” parameter, you can specify a timestamp column or any built-in date time function like NOW(), CURRENT_TIMESTAMP, etc. While the ‘formatMask’ parameter represents a valid timestamp format as described in the official Postgres documentation.

Example: How to Convert a CURRENT_TIMESTAMP to a String in Postgres?

In this example, we will pass the CURRENT_TIMESTAMP function as the first argument and a valid format as a second argument to the TO_CHAR() function:

SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY/MM/DD HH12:MM:SS');

On successful execution of the TO_CHAR() function, the current date and time will be converted into a string:

The output snippet shows that the specified timestamp was successfully converted into the string.

Example 2: How to Convert a TIMESTAMP Column to a String in Postgres?

First, let’s create a sample table with three columns: a_id, a_name, and p_date:

CREATE TABLE article_information(
a_id SERIAL PRIMARY KEY, 
a_title TEXT,
p_date TIMESTAMP);

The “a_id” column will accept numeric data, the “a_title” column will accept the textual data, and the “p_date” column will accept the TIMESTAMP values:

The table named “article_information” has been successfully created via the CREATE TABLE command. Now, execute the INSERT INTO command to insert some records into the newly created table:

INSERT INTO article_information(a_title, p_date)
VALUES ('IF Statement in Postgres', '2022-07-01 12:30:12'),
('How to Count Unique Values in Postgres', '2022-08-28 09:00:00'),
('How to Delete Duplicates in Postgres', '2022-09-15 11:10:50'),
('AVG() Function in PostgreSQL', '2022-07-10 12:12:52'),
('Delete Table PostgreSQL', '2022-07-01 12:24:12');

Now execute the “SELECT *” query to fetch all the records of the “article_information” table.

SELECT * FROM article_information;

The output snippet shows that the “p_date” column has a TIMESTAMP data type. Let’s say we need to convert the “p-date” column into a string type. For this purpose, we will use the TO_CHAR() function as follows:

SELECT a_title, p_date, TO_CHAR(p_date, 'YYYY/MM/DD HH:MM:SS') AS publised_date
FROM article_information;

The output snippet proves that the given TIMESTAMP has been converted into a string successfully.

That’s it from this Postgres blog!

Conclusion

PostgreSQL provides a built-in TO_CHAR() function that converts the given timestamp to a string. The TO_CHAR() function utilizes a format mask to convert the input value to a string. The format mask must be a valid number or date. This Postgres blog explained the working of the TO_CHAR() function for converting a TIMESTAMP to a string via practical examples.