TIMESTAMP type

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 values supported by timestamp literals is January 1, 0 CE to December 31 9999 CE. However, the type supports June 23 -5877641 CE to July 11 +5881580 CE through date arithmetic.

Literals

TIMESTAMP { '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]'}

yyyy: A four digit year between 0000 and 9999.

[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 represent a proper timestamp Databricks raises an error.

Notes

Timestamps with local timezone are 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'9999' + INTERVAL '1' YEAR;
  +10000-01-01 00:00:00