PostgreSQL TO_TIMESTAMP Function

Summary: in this tutorial, you will learn how to use the PostgreSQL TO_TIMESTAMP() function to convert a string to a timestamp based on a specified format

The PostgreSQL TO_TIMESTAMP() function converts a string to a timestamp according to the specified format.

Syntax

The following illustrates the syntax of TO_TIMESTAMP() function:

TO_TIMESTAMP(timestamp, format)

Arguments

The TO_TIMESTAMP() function requires two arguments:

1) timestamp

The timestamp is a string that represents a timestamp value in the format specified by format.

2) format

The format for timestamp argument.

To construct format strings, you use the following template patterns for formatting date and time values.

Return Value

The PostgreSQL TO_TIMESTAMP() function returns a timestamp with the time zone.

Examples

The following statement uses the TO_TIMESTAMP() function to convert a string to a timestamp:

SELECT TO_TIMESTAMP(
    '2017-03-31 9:30:20',
    'YYYY-MM-DD HH:MI:SS'
);Code language: SQL (Structured Query Language) (sql)

Output:

      to_timestamp
------------------------
 2017-03-31 09:30:20-07
(1 row)Code language: CSS (css)

In this example:

• YYYY is the four-digit year 2017
• MM is the month 03
• DD is the day 31
• HH is the hour 9
• MI is the minute 30
• SS is the second 20

Remarks

1) The TO_TIMESTAMP() function skips spaces in the input string unless the fixed format global option (FX prefix) is used.

This example uses multiple spaces in the input string:

SELECT 
    TO_TIMESTAMP('2017     Aug','YYYY MON');Code language: JavaScript (javascript)

The TO_TIMESTAMP() function just omits the spaces and returns the correct timestamp value:

      to_timestamp
------------------------
 2017-08-01 00:00:00-07
(1 row)Code language: CSS (css)

However, the following example returns an error:

SELECT 
    TO_TIMESTAMP('2017     Aug','FXYYYY MON');Code language: JavaScript (javascript)

Output:

ERROR:  invalid value "" for "MON"
DETAIL:  The given value did not match any of the allowed values for this field.
SQL state: 22007Code language: Shell Session (shell)

Because the FX option instructs the TO_TIMESTAMP() to accept the input string with one space only.

2) The TO_TIMESTAMP() function validates the input string with minimal error checking. It will try to convert the input string to a valid timestamp as much as possible that sometimes yields unexpected results.

The following example uses an invalid timestamp value:

SELECT 
    TO_TIMESTAMP('2017-02-31 30:8:00', 'YYYY-MM-DD HH24:MI:SS');Code language: JavaScript (javascript)

It returns an error:

ERROR:  date/time field value out of range: "2017-02-31 30:8:00"
SQL state: 22008Code language: Shell Session (shell)

3) When converting a string to a timestamp, the TO_TIMESTAMP() function treats milliseconds or microseconds as seconds after the decimal point.

SELECT 
    TO_TIMESTAMP('01-01-2017 10:2', 'DD-MM-YYYY SS:MS');Code language: JavaScript (javascript)

The result is:

to_timestamp
--------------------------
 2017-01-01 00:00:10.2-07Code language: CSS (css)

In this example, 2 is not 2 milliseconds but 200. It means that:

SELECT 
        TO_TIMESTAMP('01-01-2017 10:2', 'DD-MM-YYYY SS:MS');Code language: JavaScript (javascript)

and

SELECT 
        TO_TIMESTAMP('01-01-2017 10:200', 'DD-MM-YYYY SS:MS');Code language: JavaScript (javascript)

returns the same result.

2017-01-01 00:00:10.2-07Code language: Shell Session (shell)

To get 2 milliseconds, you must use 01-01-2017 10:002. In this case, 002 is interpreted as 0.002 seconds, equivalent to 2 milliseconds.

4) If the year is less than four digits, the TO_TIMESTAMP() will adjust it to the nearest year e.g., 99 becomes 1999, 17 becomes 2017.

SELECT
    TO_TIMESTAMP('12 31 99 12:45', 'MM DD YY HH:MI');Code language: JavaScript (javascript)

Output:

      to_timestamp
------------------------
 1999-12-31 00:45:00-07
(1 row)Code language: CSS (css)

Consider the following example:

SELECT
      TO_TIMESTAMP('12 31 16 12:45', 'MM DD YY HH:MI');Code language: JavaScript (javascript)

The nearest year of 16 is 2016, therefore, it returns the following result:

to_timestamp
------------------------
 2016-12-31 00:45:00-07Code language: CSS (css)

In this tutorial, you have learned how to use the PostgreSQL TO_TIMESTAMP() function to convert a string to a timestamp.