Share via

parse_timestamp function

Applies to: check marked yes Databricks Runtime 18.1 and above

If expr is a string, parses it into a TIMESTAMP according to the first matching pattern in the given list of formats. One or more of the formats can reference a predefined list of formats. If expr is a numeric type, parses it as a Unix timestamp.

Syntax

parse_timestamp(expr [, { pattern [...] } ])

Arguments

  • expr: A string or numeric value to parse into a TIMESTAMP. Accepted types are:
    • TINYINT, SMALLINT, INT, BIGINT
    • FLOAT, DOUBLE
    • DECIMAL (up to 18 precision)
    • STRING
  • pattern: An optional list of timestamp patterns or a reference to a predefined list such as #iso8601 or #rfc3339. The timestamp patterns must be a constant string. If no pattern is specified, it is equivalent to specifying #extended.

Returns

A TIMESTAMP representing the parsed expr.

If the expr is a string, it is parsed according to the first matching pattern. If none of the patterns match, the function raises an error.

If the expr is numeric, it is always parsed as a Unix timestamp.

If expr is NULL, the function returns NULL.

To return NULL instead of an error when parsing fails, use try_parse_timestamp function.

Notes

  • If the pattern list contains NULL values, they are ignored. If all provided patterns are NULL, the result is NULL.
  • If the timestamp matches a pattern containing no timezone information, it is parsed in the session timezone.

Error conditions

Examples

-- Parse timestamp according to default pattern list
> SELECT parse_timestamp('2024-12-09T19:30:01');
 2024-12-09 19:30:01.000

-- Parse a microsecond epoch timestamp using the unix pattern list
> SELECT parse_timestamp('1733772601000000', '#unix');
 2024-12-09 19:30:01.000

-- Bad `expr` that does not match any pattern
> SELECT parse_timestamp('bad input');
  Error: CANNOT_PARSE_TIMESTAMP

-- Use try_parse_timestamp to return NULL instead of error
> SELECT try_parse_timestamp('bad input');
 NULL

-- Extend the default pattern list with custom patterns
> SELECT parse_timestamp(col, "#extended", "dd MMM, yyyy", "MMM d; yy") FROM VALUES ('2024-12-01'), ('02 Dec, 2024'), ('DEC 3; 24') AS t(col)
 2024-12-01 00:00:00.000
 2024-12-02 00:00:00.000
 2024-12-03 00:00:00.000

Pattern reference

parse_timestamp and try_parse_timestamp function use a pattern set that differs in some ways from the standard datetime pattern used by to_timestamp. The following table lists all pattern symbols and whether each is supported:

Symbol Meaning Examples Support
U unix See note below Supported
G era AD; Anno Domini Not supported
y year 2020; 20 Supported
D day-of-year 189 Not supported
M/L month-of-year 7; 07; Jul; July Supported
d day-of-month 28 Supported
Q/q quarter-of-year 3; 03; Q3; 3rd quarter Not supported
E day-of-week Tue; Tuesday Supported
F aligned day of week in month 3 Not supported
a am-pm-of-day PM Supported
h clock-hour-of-am-pm (1-12) 12 Supported
K hour-of-am-pm (0-11) 0 Supported
k clock-hour-of-day (1-24) 1 Not supported
H hour-of-day (0-23) 0 Supported
m minute-of-hour 30 Supported
s second-of-minute 55 Supported
S fraction-of-second 978 Supported
V time-zone ID America/Los_Angeles; Z; -08:30 Supported
z time-zone name Pacific Standard Time; PST Supported
O localized zone-offset GMT+8; GMT+08:00; UTC-08:00 Supported
X zone-offset 'Z' for zero Z; -08; -0830; -08:30 Supported (1-3 digits)
x zone-offset +0000; -08; -0830; -08:30 Supported (1-3 digits)
Z zone-offset +0000; -0800; -08:00 Supported
# pattern list #common, #iso8601 Supported

Note

Unix pattern (U): The #unix pattern list parses Unix epoch timestamps. For dates between 1971-08-03 and 3554-06-09, the implementation can disambiguate and parse Unix timestamps in seconds, microseconds, and nanoseconds. Dates outside that range may fail to parse or parse incorrectly. Negative integers are not supported. Fractional values are supported up to 9 decimal places and are truncated to match the precision of the result type.

Locale: Pattern placeholders are not locale-aware. Month and day names are supported in English only.

Pattern lists

You can pass a predefined pattern list by name (for example #extended, #iso8601, #unix). The following pattern lists are available:

  • common — Commonly found patterns not defined by a standard
  • iso8601
  • rfc1036, rfc1123, rfc2822, rfc3164, rfc3339, rfc4287, rfc5322, rfc5424, rfc6265, rfc7231, rfc822, rfc850, rfc9110, rfc9557
  • unix — Unix epoch timestamps only
  • extended — Union of all of the above lists; also the default when no pattern is specified
  • extended-dmy — Extended with DMY locale-specific formats
  • extended-mdy — Extended with MDY locale-specific formats
  • Last updated on