How to Convert a String to a Timestamp in PostgreSQL

Problem:

You’d like to convert a string containing datetime information to a timestamp in PostgreSQL.

Let’s convert a string containing date, time, and time zone information to the timestamptz data type.

Solution:

We’ll use the TO_TIMESTAMP() function. Here’s the query you would write:

SELECT TO_TIMESTAMP('2018/08/27/15:23:45', 'YYYY/MM/DD/HH24:MI:ss')
         AS new_timestamptz;

Here’s the result of the query:

Discussion:

Use the PostgreSQL function TO_TIMESTAMP() when you want to convert a string containing date and time data to the timestamp data type. This function takes two arguments: a string containing a date and time (in our example, the text '2018/08/27/15:23:45') and the input format (in our example, 'YYYY/MM/DD/HH24:MI:ss'). The input format indicates how the characters in the string should be converted. Here are the main elements from the above pattern:

• YYYY represents a 4-digit year.
• MM represents a 2-digit month.
• DD represents a 2-digit day of the month.
• HH24 represents a 2-digit hour (from 00 to 23).
• MI represents a 2-digit minute (from 00 to 59).
• ss represents a 2-digit second (from 00 to 59).

Note that we use slashes (/) as date part delimiters and colons (:) as time part delimiters. You can find a complete list of datetime pattern elements in the PostgreSQL documentation.

Notice that the input format is a string. The function TO_TIMESTAMP() returns a timestamptz value with time zone information.

In our example, the text date and time '2018/08/27/15:23:45' was converted to the timestamp value 2018-08-27 15:23:45+02. The timestamp data type is more readable.