Skip to main content
Last updated on

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​

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