TO_TIMESTAMP

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

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

Behavior type

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)