parse_timestamp

Description

Parse a string value, whose format is specified by the format argument, as a timestamp.

Format specifiers are detailed in Snowflake’s time format specifiers documentation.

The format and timezone arguments must be string constants.

If the string value does not match the format of format, the result of parse_timestamp will be NULL.

If the string value does not contain a timezone, the timezone may be specified in the optional timezone argument. parse_timestamp will throw an error if the format argument contains a timezone and the timezone argument is also supplied. If the format argument does not contain a timezone, and timezone is also not supplied, the string value will be interpreted as UTC.

If supplied, the timezone argument must exactly match one of the following formats: “07”, “+07”, “-07” (2 digit hour offset from UTC, optionally preceded by a ‘+’ or ‘-‘), “0700”, “+0700”, “-0700”, “07:00”, “+07:00”, “-07:00” (2 digit hour and 2 digit minute offset from UTC, optionally separated by a colon, optionally preceded by a ‘+’ or ‘-‘)

Return type

timestamp

Domain

This is a scalar function (calculates a single output value for a single input row.)

Categories

Usage

parse_timestamp(value, format, [ timezone ])

Argument

Type

Optional

Repeatable

Restrictions

value

string

no

no

none

format

string

no

no

constant

timezone

string

yes

no

constant

Examples

make_col time:parse_timestamp("2022-09-19 18:38:59.059-05:00", "YYYY-MM-DD HH24:MI:SS.FF3TZH:TZM")

Make a new column time, of type timestamp, by parsing the string value “2022-09-19 18:38:59.059-05:00” according to the given format string.

make_col time:parse_timestamp("2022-09-19 18:38:59.059", "YYYY-MM-DD HH24:MI:SS.FF3")

Make a new column time, of type timestamp, by parsing the string value “2022-09-19 18:38:59.059” according to the given format string. Since the format string contains no timezone specifier, and the timezone argument is not supplied, the value will be interpreted as UTC.

make_col time:parse_timestamp("2022-09-19 18:38:59.059", "YYYY-MM-DD HH24:MI:SS.FF3", "-05:00")

Make a new column time, of type timestamp, by parsing the string value “2022-09-19 18:38:59.059” according to the given format string. The value is interpreted to be in timezone UTC-05:00 based on the timezone argument.

make_col time:parse_timestamp("Tue, 18 Oct 2022 13:41:26 -0700", "DY, DD MON YYYY HH24:MI:SS TZHTZM")

Make a new column time, of type timestamp, by parsing the string value “Tue, 18 Oct 2022 13:41:26 -0700” according to the given RFC-2822 format string.