TO_TIMESTAMP

The TO_TIMESTAMP function converts a string value or a UNIX/POSIX epoch value to a TIMESTAMP WITH TIME ZONE type.

Syntax

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

Semantics

expression	(CHAR or VARCHAR) is the string to convert
pattern	(CHAR or VARCHAR) specifies an output pattern string using the Template Patterns for Date/Time Formatting and/or Template Patterns for Numeric Formatting.
unix-epoch	(DOUBLE PRECISION) specifies some number of seconds elapsed since midnight UTC of January 1, 1970, not counting leap seconds. `INTEGER` values are implicitly cast to `DOUBLE PRECISION`.

Notes

For more information about UNIX/POSIX time, see the Wikipedia.
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, one must 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 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, but 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 some non-digit character or template after YYYY, otherwise the year is always interpreted as four digits. For example (with the year 20000): to_date('200001131', 'YYYYMMDD') will be interpreted as a four-digit year; instead use a non-digit separator after the year, like to_date('20000-1131', 'YYYY-MMDD') or to_date('20000Nov31', 'YYYYMonDD').
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

TO_TIMESTAMP('05 Dec 2000', 'DD Mon YYYY')

TO_TIMESTAMP(200120400)