PostgreSQL TO_TIMESTAMP Function

Summary: In this tutorial, you’ll learn how to convert a string to a timestamp based on a specified format using the TO_TIMESTAMP function.

PostgreSQL TO_TIMESTAMP Function Overview #

The TO_TIMESTAMP function converts a string to a timestamp based on a specified format.

Here’s the syntax of the TO_TIMESTAMP function:

TO_TIMESTAMP(text, format)

The TO_TIMESTAMP function takes two parameters:

text is a string you want to convert into a timestamp.
format is a string that represents the format of the timestamp string.

The TO_TIMESTAMP function returns a value of the TIMESTAMP WITH A TIME ZONE represented by the timestamp string (text) in the specified format.

The TO_TIMESTAMP function can be helpful when importing text data from an external source (e.g., CSV files, web pages) into PostgreSQL and storing it as timestamps.

PostgreSQL TO_TIMESTAMP Function Example #

First, show the current time zone:

SHOW TIMEZONE;

Output:

  TimeZone
------------
 US/Pacific

Second, convert a string to a timestamp using the TO_TIMESTAMP function:

SELECT
  TO_TIMESTAMP('2025-03-19 10:30:45', 'YYYY-MM-DD HH:MI:SS') result;Code language: JavaScript (javascript)

Output:

         result
------------------------
 2025-03-19 10:30:45-07
Code language: CSS (css)

In this example, we use the format pattern (YYYY-MM-DD HH:MI:SS) to convert the string '2025-03-19 10:30:45' to a timestamp.

In the format pattern YYYY-MM-DD HH:MI:SS:

YYYY is the four-digit year 2025
MM is the month 03
DD is the day 19
HH is the hour 10
MI is the minute 30
SS is the second 45

You can use the format patterns to construct the timestamp format string.

The TO_TIMESTAMP function takes the session’s time zone and uses it for the resulting timestamp.

Summary #

Use the PostgreSQL TO_TIMESTAMP() function to convert a string to a timestamp according to a format pattern.

Quiz #

Was this tutorial helpful ?