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 , DATE , TO_TIME , TIME

Syntax

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

timestampFunction ( <date_expr> )

timestampFunction ( <timestamp_expr> )

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

timestampFunction ( '<integer>' )

timestampFunction ( <variant_expr> )

Where:

timestampFunction ::=
    TO_TIMESTAMP | TO_TIMESTAMP_LTZ | TO_TIMESTAMP_NTZ | TO_TIMESTAMP_TZ

Arguments

Required:

One of:

numeric_expr

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

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).

string_expr

String from which to extract a timestamp, for example ‘2019-01-31 01:02:03.004’.

integer

An expression that evaluates to a string containing an integer, for example ‘15000000’. Depending upon the magnitude of the string, it can be interpreted as seconds, milliseconds, microseconds, or nanoseconds. For details, see the Usage Notes below.

variant_expr

An expression of type VARIANT. The VARIANT must contain one of the following:

  • A string from which to extract a timestamp.

  • A timestamp.

  • An integer number of seconds or milliseconds.

  • A string containing an integer number of seconds or milliseconds.

Although TO_TIMESTAMP accepts a DATE value, it does not accept a DATE inside a VARIANT.

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 midnight of a given day will be used, according to the specific timestamp flavor (NTZ/LTZ/TZ) 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 JSON null value, the result will be NULL.

      • 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.

        Note

        When an INTEGER value is cast directly to TIMESTAMP_NTZ, the integer is treated as the number of seconds since the beginning of the Linux epoch, and the local time zone is not taken into account. (For details, see TO_TIMESTAMP / TO_TIMESTAMP_*.) However, if the INTEGER value is stored inside a VARIANT value, for example as shown below, then the conversion is indirect, and is affected by the local time zone, even though the final result is TIMESTAMP_NTZ:

        select to_timestamp(parse_json(31000000));
        select parse_json(31000000)::timestamp_ntz;
        

        To convert independently of the local time zone, add an explicit cast to integer in the expression, as shown below:

        select to_timestamp(parse_json(31000000)::int);
        select parse_json(31000000)::int::timestamp_ntz;
        

        This applies whether casting to TIMESTAMP_NTZ or calling the function TO_TIMESTAMP_NTZ(). It also applies when calling TO_TIMESTAMP() when the TIMESTAMP_TYPE_MAPPING parameter is set to TIMESTAMP_NTZ.

        For an example with output, see the examples at the end of this topic.

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

  • If the format of the input parameter is a string that contains an integer:

    • After the string is converted to an integer, the integer is treated as a number of seconds, milliseconds, microseconds, or nanoseconds after the start of the Unix epoch (1970-01-01 00:00:00.000000000 UTC).

      • If the integer is less than 31536000000 (the number of milliseconds in a year), then the value is treated as a number of seconds.

      • If the value is greater than or equal to 31536000000 and less than 31536000000000, then the value is treated as milliseconds.

      • If the value is greater than or equal to 31536000000000 and less than 31536000000000000, then the value is treated as microseconds.

      • If the value is greater than or equal to 31536000000000000, then the value is treated as nanoseconds.

    • If more than one row is evaluated (for example, if the input is the column name of a table that contains more than two rows), the first processed value determines whether all subsequent values are treated as seconds, milliseconds, microseconds, or nanoseconds.

      If the first value is greater than or equal to 31536000000, then all values will be treated as milliseconds, even if some remaining values are less than 31536000000. Similar logic applies for microseconds and nanoseconds.

    Caution

    Currently, negative values are always treated as seconds. For example, -31536000000000000000 is treated as a number of seconds before the year 1970, although its scale implies that it’s intended to be used as nanoseconds.

    This behavior might change in the future.

    Caution

    Deprecation Warning: Future versions of Snowflake might automatically interpret stringified integer values as seconds, rather than as milliseconds, microseconds, or nanoseconds. Snowflake recommends that you call TO_DATE or TO_TIMESTAMP with strings that contain integers only when those integers are intended to be interpreted as seconds.

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                               |
+-------------------------------------------------------+

This example shows how the first value converted can control whether the subsequent values are interpreted as seconds or milliseconds:

Create and load the table:

CREATE TABLE demo1 (
    description VARCHAR,
    value VARCHAR -- yes, string rather than bigint
    );

Contrast the output of the following two queries. The only difference between the two is the order of the rows:

SELECT 
       description, 
       value, 
       TO_TIMESTAMP(value), 
       TO_DATE(value)
    FROM demo1
    ORDER BY value ASC
    ;
+--------------+-------------+-------------------------+----------------+
| DESCRIPTION  | VALUE       | TO_TIMESTAMP(VALUE)     | TO_DATE(VALUE) |
|--------------+-------------+-------------------------+----------------|
| Seconds      | 31535999999 | 2969-05-02 23:59:59.000 | 2969-05-02     |
| Milliseconds | 31536000000 | 2969-05-03 00:00:00.000 | 2969-05-03     |
+--------------+-------------+-------------------------+----------------+
SELECT 
       description, 
       value, 
       TO_TIMESTAMP(value), 
       TO_DATE(value)
    FROM demo1
    ORDER BY value DESC
    ;
+--------------+-------------+-------------------------+----------------+
| DESCRIPTION  | VALUE       | TO_TIMESTAMP(VALUE)     | TO_DATE(VALUE) |
|--------------+-------------+-------------------------+----------------|
| Milliseconds | 31536000000 | 1971-01-01 00:00:00.000 | 1971-01-01     |
| Seconds      | 31535999999 | 1970-12-31 23:59:59.999 | 1970-12-31     |
+--------------+-------------+-------------------------+----------------+

The following example shows the difference in behavior between using an integer and using a variant that contains an integer:

SELECT 0::TIMESTAMP_NTZ, PARSE_JSON(0)::TIMESTAMP_NTZ, PARSE_JSON(0)::INT::TIMESTAMP_NTZ;
+-------------------------+------------------------------+-----------------------------------+
| 0::TIMESTAMP_NTZ        | PARSE_JSON(0)::TIMESTAMP_NTZ | PARSE_JSON(0)::INT::TIMESTAMP_NTZ |
|-------------------------+------------------------------+-----------------------------------|
| 1970-01-01 00:00:00.000 | 1969-12-31 16:00:00.000      | 1970-01-01 00:00:00.000           |
+-------------------------+------------------------------+-----------------------------------+
SELECT TO_TIMESTAMP_NTZ(0), TO_TIMESTAMP_NTZ(PARSE_JSON(0)), TO_TIMESTAMP_NTZ(PARSE_JSON(0)::INT);
+-------------------------+---------------------------------+--------------------------------------+
| TO_TIMESTAMP_NTZ(0)     | TO_TIMESTAMP_NTZ(PARSE_JSON(0)) | TO_TIMESTAMP_NTZ(PARSE_JSON(0)::INT) |
|-------------------------+---------------------------------+--------------------------------------|
| 1970-01-01 00:00:00.000 | 1969-12-31 16:00:00.000         | 1970-01-01 00:00:00.000              |
+-------------------------+---------------------------------+--------------------------------------+