Categories:

Conversion Functions , Date & Time Functions

TO_TIMESTAMP / TO_TIMESTAMP_*

Converts an input expression into the corresponding timestamp:

  • TO_TIMESTAMP_LTZ (timestamp with local time zone)

  • TO_TIMESTAMP_NTZ (timestamp with no time zone)

  • TO_TIMESTAMP_TZ (timestamp with time zone)

Note

TO_TIMESTAMP maps to one of the other timestamp functions, based on the TIMESTAMP_TYPE_MAPPING session parameter. The parameter default is TIMESTAMP_NTZ so TO_TIMESTAMP maps to TO_TIMESTAMP_NTZ by default.

See also:

TRY_TO_TIMESTAMP / TRY_TO_TIMESTAMP_* ,

AS_TIMESTAMP_* , IS_TIMESTAMP_* ,

TO_DATE , TO_TIME

Syntax

timestampFunction ( <string_expr> [ , <format> ] )

timestampFunction ( <numeric_expr> [ , <scale> ] )

timestampFunction ( <date_expr> )

timestampFunction ( <timestamp_expr> )

timestampFunction ( <variant_expr> )

Where:

timestampFunction ::=
    TO_TIMESTAMP | TO_TIMESTAMP_LTZ | TO_TIMESTAMP_NTZ | TO_TIMESTAMP_TZ

Arguments

Required:

One of:

string_expr

String from which to extract a timestamp.

date_expr

A date to be converted into a timestamp.

timestamp_expr

A timestamp to be converted into another timestamp (e.g. convert TIMESTAMP_LTZ to TIMESTAMP_NTZ).

numeric_expr

A number of seconds (if scale = 0 or is absent) or fractions of a second since the start of the EPOCH (1970-01-01 00:00:00 UTC). If a non-integer decimal expression is input, the scale of the result is inherited.

variant_expr

A variant value.

Optional:

format

Format specifier (only for string_expr). For more information, see Date and Time Formats in Conversion Functions.

The default value is the current value of the TIMESTAMP_INPUT_FORMAT parameter (usually AUTO).

scale

Scale specifier (only for numeric_expr). If specified, defines the scale of the numbers provided. For example:

  • For seconds, scale = 0.

  • For milliseconds, scale = 3.

  • For nanoseconds, scale = 9.

Usage Notes

This family of functions returns timestamp values, specifically:

  • For NULL input the result will be NULL.

  • For string_expr: timestamp represented by a given string. If the string does not have a time component, midnight will be used.

  • For date_expr: timestamp representing a midnight of a given day will be used, according to the specific timestamp flavor semantics.

  • For timestamp_expr: a timestamp with possibly different flavor than the source timestamp.

  • For numeric_expr: a timestamp representing the number of seconds (or fractions of a second) provided by the user. Note, that UTC time is always used to build the result.

  • For variant_expr:

    • If the variant contains a timestamp value of the same kind as the result, this value will be preserved as is.

    • If the variant contains a timestamp value of the different kind, the conversion will be done in the same way as from timestamp_expr.

    • If the variant contains a string, conversion from a string value will be performed (using automatic format).

    • If the variant contains a number, conversion as if from numeric_expr will be performed.

    • If the variant contains JSON null value, the result will be NULL.

  • If conversion is not possible, an error is returned.

Examples

This shows that TO_TIMESTAMP_TZ creates a timestamp that contains a time zone from the session, but the value from TO_TIMESTAMP_NTZ does not have a time zone:

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
SELECT TO_TIMESTAMP_TZ('2013-04-05 01:02:03');
+----------------------------------------+
| TO_TIMESTAMP_TZ('2013-04-05 01:02:03') |
|----------------------------------------|
| 2013-04-05 01:02:03.000 -0700          |
+----------------------------------------+

SELECT TO_TIMESTAMP_NTZ('2013-04-05 01:02:03');
+-----------------------------------------+
| TO_TIMESTAMP_NTZ('2013-04-05 01:02:03') |
|-----------------------------------------|
| 2013-04-05 01:02:03.000                 |
+-----------------------------------------+

This shows how different formats can influence parsing of an ambiguous date:

SELECT TO_TIMESTAMP_TZ('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss');
+-----------------------------------------------------------------+
| TO_TIMESTAMP_TZ('04/05/2013 01:02:03', 'MM/DD/YYYY HH24:MI:SS') |
|-----------------------------------------------------------------|
| 2013-04-05 01:02:03.000 -0700                                   |
+-----------------------------------------------------------------+

SELECT TO_TIMESTAMP_TZ('04/05/2013 01:02:03', 'dd/mm/yyyy hh24:mi:ss');
+-----------------------------------------------------------------+
| TO_TIMESTAMP_TZ('04/05/2013 01:02:03', 'DD/MM/YYYY HH24:MI:SS') |
|-----------------------------------------------------------------|
| 2013-05-04 01:02:03.000 -0700                                   |
+-----------------------------------------------------------------+

This shows how to use a numeric input that represents approximately 40 years from midnight January 1, 1970 (the start of the UNIX EPOCH):

ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM TZD';
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
SELECT TO_TIMESTAMP_NTZ(40 * 365.25 * 86400);
+---------------------------------------+
| TO_TIMESTAMP_NTZ(40 * 365.25 * 86400) |
|---------------------------------------|
| 2010-01-01 00:00:00.000               |
+---------------------------------------+

This is similar to the preceding, but provides the value as milliseconds by supplying a scale value of 3:

ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM TZD';
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
SELECT TO_TIMESTAMP_NTZ(40 * 365.25 * 86400 * 1000 + 456, 3);
+-------------------------------------------------------+
| TO_TIMESTAMP_NTZ(40 * 365.25 * 86400 * 1000 + 456, 3) |
|-------------------------------------------------------|
| 2010-01-01 00:00:00.456                               |
+-------------------------------------------------------+