`TIMESTAMP` type

Applies to: check marked yes Databricks SQL Databricks Runtime

Represents values comprising values of fields year, month, day, hour, minute, and second, with the session local time-zone. The timestamp value represents an absolute point in time.

Syntax

TIMESTAMP

Limits

The range of timestamps supported is -290308-12-21 BCE 19:59:06 GMT to +294247-01-10 CE 04:00:54 GMT.

Literals

TIMESTAMP timestampString

timestampString
{ '[+|-]yyyy[...]' |
  '[+|-]yyyy[...]-[m]m' |
  '[+|-]yyyy[...]-[m]m-[d]d' |
  '[+|-]yyyy[...]-[m]m-[d]d ' |
  '[+|-]yyyy[...]-[m]m-[d]d[T][h]h[:]' |
  '[+|-]yyyy[..]-[m]m-[d]d[T][h]h:[m]m[:]' |
  '[+|-]yyyy[...]-[m]m-[d]d[T][h]h:[m]m:[s]s[.]' |
  '[+|-]yyyy[...]-[m]m-[d]d[T][h]h:[m]m:[s]s.[ms][ms][ms][us][us][us][zoneId]' }

+ or -: An optional sign. - indicates BCE, + indicates CE (default).
yyyy: A year comprising at least four digits.
[m]m: A one or two digit month between 01 and 12.
[d]d: A one or two digit day between 01 and 31.
h[h]: A one or two digit hour between 00 and 23.
m[m]: A one or two digit minute between 00 and 59.
s[s]: A one or two digit second between 00 and 59.
[ms][ms][ms][us][us][us]: Up to 6 digits of fractional seconds.

zoneId:

Z - Zulu time zone UTC+0
+|-[h]h:[m]m
An ID with one of the prefixes UTC+, UTC-, GMT+, GMT-, UT+ or UT-, and a suffix in the formats:
- +|-h[h]
- +|-hh[:]mm
- +|-hh:mm:ss
- +|-hhmmss
Region-based zone IDs in the form <area>/<city>, for example, Europe/Paris.

If the month or day components are not specified they default to 1. If hour, minute, or second components are not specified they default to 0. If no zoneId is specified it defaults to session time zone,

If the literal does not represent a proper timestamp Databricks raises an error.

Notes

Timestamps with local timezone are internally normalized and persisted in UTC. Whenever the value or a portion of it is extracted the local session timezone is applied.

Examples

> SELECT TIMESTAMP'0000';
  0000-01-01 00:00:00

> SELECT TIMESTAMP'2020-12-31';
  2020-12-31 00:00:00

> SELECT TIMESTAMP'2021-7-1T8:43:28.123456';
  2021-07-01 08:43:28.123456

> SELECT current_timezone(), TIMESTAMP'2021-7-1T8:43:28UTC+3';
  America/Los_Angeles 2021-06-30 22:43:28

> SELECT CAST('1908-03-15 10:1:17' AS TIMESTAMP)
  1908-03-15 10:01:17

> SELECT TIMESTAMP'+10000';
  +10000-01-01 00:00:00

TIMESTAMP type