TIMESTAMP type

Applies to: check marked yes Databricks SQL check marked yes 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 [ (p) ] | TIMESTAMP_LTZ [ (p) ]
  • p: An optional precision from 6 through 9, representing the number of fractional-second digits. The default is 6. Specify 9 for nanosecond precision.

Important

TIMESTAMP(p) with precision 7–9 is in Beta.

Limits

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

Note

When writing to Delta or Parquet, TIMESTAMP(p) values with precision greater than 6 are limited to years 1677 through 2262.

Literals

TIMESTAMP timestampString

TIMESTAMP_LTZ 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][ns][ns][ns][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][ns][ns][ns]: Up to 9 digits of fractional seconds. Specify exactly nine fractional digits to construct a TIMESTAMP(9) literal.

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 Azure 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.

To use TIMESTAMP(9) columns in Delta Lake tables, enable the timestamp precision feature:

ALTER TABLE table_name SET TBLPROPERTIES ('delta.feature.timestampPrecision' = 'supported')

Enabling the feature upgrades the table protocol. Clients that do not support the feature cannot read or write the table. See Delta Lake feature compatibility and protocols.

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

> SELECT typeof(TIMESTAMP'2026-08-18 14:30:45.123456789');
  TIMESTAMP(9)

> SELECT CAST('2026-08-18 14:30:45.123456789' AS TIMESTAMP(9));
  2026-08-18 14:30:45.123456789

TIMESTAMP(9) table limitations

  • Iceberg tables are not supported.
  • Generated columns in Delta Lake tables are not supported.
  • Liquid clustering columns in Delta Lake tables are not supported.
  • Last updated on