TO_TIMESTAMP / TO_TIMESTAMP_*¶

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

Copy

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.

Note

The use of quoted integers as inputs is deprecated.

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.

Returns¶

The data type of the returned value is one of the TIMESTAMP data types. By default, the data type is TIMESTAMP_NTZ. You can change this by setting the session parameter TIMESTAMP_TYPE_MAPPING.

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;
      

      Copy

      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;
      

      Copy

      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.

    Note

    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, TO_TIME, or TO_TIMESTAMP with strings that contain integers only when those integers are intended to be interpreted as seconds.

  • If more than one row is evaluated (for example, if the input is the column name of a table that contains more than one row), each value is examined independently to determine if the value represents seconds, milliseconds, microseconds, or nanoseconds.

• When you use the TO_TIMESTAMP_NTZ or TRY_TO_TIMESTAMP_NTZ function to convert a timestamp with time zone information, the time zone information is lost. If the timestamp is then converted back to a timestamp with time zone information (by using the TO_TIMESTAMP_TZ function for example), the time zone information is not recoverable.

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';
+----------------------------------+
| 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';
+----------------------------------+
| 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 function determines the units to use (seconds, milliseconds, microseconds, or nanoseconds), based on the magnitude of the value:

Create and load the table with strings containing integers within different ranges:

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

INSERT INTO demo1 (description, value) VALUES
   ('Seconds',      '31536000'),
   ('Milliseconds', '31536000000'),
   ('Microseconds', '31536000000000'),
   ('Nanoseconds',  '31536000000000000')
   ;

Copy

Pass the strings to the function:

SELECT
       description,
       value,
       TO_TIMESTAMP(value),
       TO_DATE(value)
    FROM demo1
    ORDER BY value
    ;
+--------------+-------------------+-------------------------+----------------+
| DESCRIPTION  | VALUE             | TO_TIMESTAMP(VALUE)     | TO_DATE(VALUE) |
|--------------+-------------------+-------------------------+----------------|
| Seconds      | 31536000          | 1971-01-01 00:00:00.000 | 1971-01-01     |
| Milliseconds | 31536000000       | 1971-01-01 00:00:00.000 | 1971-01-01     |
| Microseconds | 31536000000000    | 1971-01-01 00:00:00.000 | 1971-01-01     |
| Nanoseconds  | 31536000000000000 | 1971-01-01 00:00:00.000 | 1971-01-01     |
+--------------+-------------------+-------------------------+----------------+

Copy

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

Copy

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

Copy