TO_TIMESTAMP

Converts a string value or a UNIX/POSIX epoch value to a TIMESTAMP type.

Behavior Type

Stable

Syntax

TO_TIMESTAMP ( { expression, pattern } | unix‑epoch )

Parameters

expression

Specifies the string value to convert, of type CHAR or VARCHAR.

pattern

A CHAR or VARCHAR that specifies an output pattern string. See:

unix‑epoch

DOUBLE PRECISION value that specifies some number of seconds elapsed since midnight UTC of January 1, 1970, excluding leap seconds. INTEGER values are implicitly cast to DOUBLE PRECISION.

Notes

  • Millisecond (MS) and microsecond (US) values in a conversion from string to TIMESTAMP are used as part of the seconds after the decimal point. For example TO_TIMESTAMP('12:3', 'SS:MS') is not 3 milliseconds, but 300, because the conversion counts it as 12 + 0.3 seconds. This means for the format SS:MS, the input values 12:3, 12:30, and 12:300 specify the same number of milliseconds. To get three milliseconds, use 12:003, which the conversion counts as 12 + 0.003 = 12.003 seconds.

    Here is a more complex example: TO_TIMESTAMP('15:12:02.020.001230', 'HH:MI:SS.MS.US') is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds + 1230 microseconds = 2.021230 seconds.

  • To use a double quote character in the output, precede it with a double backslash. This is necessary because the backslash already has a special meaning in a string constant. For example: '\\"YYYY Month\\"'
  • TO_TIMESTAMP, TO_TIMESTAMP_TZ, and TO_DATE skip multiple blank spaces in the input string if the FX option is not used. FX must be specified as the first item in the template. For example:

    • TO_TIMESTAMP('2000   JUN', 'YYYY MON') is correct.
    • TO_TIMESTAMP('2000       JUN', 'FXYYYY MON') returns an error, because TO_TIMESTAMP expects one space only.
  • The YYYY conversion from string to TIMESTAMP or DATE has a restriction if you use a year with more than four digits. You must use a non-digit character or template after YYYY, otherwise the year is always interpreted as four digits. For example, given the following arguments, TO_DATE interprets the five-digit year 20000 as a four-digit year:

    => SELECT TO_DATE('200001131','YYYYMMDD');
      TO_DATE
    ------------
     2000-01-13
    (1 row)

    Instead, use a non-digit separator after the year. For example:

    => SELECT TO_DATE('20000-1131', 'YYYY-MMDD');
       TO_DATE
    -------------
     20000-12-01
    (1 row)
    
  • In conversions from string to TIMESTAMP or DATE, the CC field is ignored if there is a YYY, YYYY or Y,YYY field. If CC is used with YY or Y, then the year is computed as (CC–1)*100+YY.

Examples

=> SELECT TO_TIMESTAMP('13 Feb 2009', 'DD Mon YYYY');
    TO_TIMESTAMP
---------------------
 1200-02-13 00:00:00
(1 row)
=> SELECT TO_TIMESTAMP(200120400);
    TO_TIMESTAMP
---------------------
 1976-05-05 01:00:00
(1 row)