Databricks TIMESTAMP Type — Time Zones, Precision and TIMESTAMP_NTZ

This post organizes the Databricks SQL language reference page TIMESTAMP type, together with the related pages for TIMESTAMP_NTZ and SET TIME ZONE, into a single reference. It applies to Databricks SQL and Databricks Runtime.

1. What TIMESTAMP represents

TIMESTAMP represents values of the fields year, month, day, hour, minute and second, with the session local time zone. A TIMESTAMP value represents an absolute point in time.

• Storage: timestamps with local time zone are internally normalized and persisted in UTC.
• Retrieval: whenever the value or a portion of it is extracted, the session time zone is applied.

TIMESTAMP [ (p) ] | TIMESTAMP_LTZ [ (p) ]

TIMESTAMP and TIMESTAMP_LTZ are the same type.

2. The session time zone

Because extraction applies the session time zone, check and set it explicitly.

SELECT current_timezone();
 
SET TIME ZONE 'Asia/Seoul';                       -- region-based zone ID
SET TIME ZONE '+08:00';                           -- zone offset
SET TIME ZONE INTERVAL '08:30:00' HOUR TO SECOND; -- interval
SET TIME ZONE LOCAL;

In Databricks SQL the system default is UTC. Other short names (such as three-letter abbreviations) are not recommended because they can be ambiguous.

3. Literals

TIMESTAMP timestampString
TIMESTAMP_LTZ timestampString

The string may run from just a year up to a full timestamp with fractional seconds and a zone:

'[+|-]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]'

• Sign: - means BCE, + (default) means CE. The year has at least four digits.
• Fields: month, day, hour, minute and second may be one or two digits.
• Fractional seconds: up to 9 digits. Exactly nine digits construct a TIMESTAMP(9) literal.
• Defaults: missing month or day default to 1; missing hour, minute or second default to 0.
• zoneId: Z (UTC+0); +|-[h]h:[m]m; an ID prefixed with UTC+, UTC-, GMT+, GMT-, UT+ or UT-; or a region-based ID such as Europe/Paris. Without a zone, the session time zone is used.
• An invalid timestamp raises an error.

> 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

4. Precision: TIMESTAMP(p)

• p is an optional precision from 6 through 9, the number of fractional-second digits. The default is 6; specify 9 for nanoseconds.
• TIMESTAMP(p) with precision 7–9 is in Beta.

> 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

Using TIMESTAMP(9) in Delta Lake tables

Enable the timestamp precision table feature:

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

Enabling the feature upgrades the table protocol. Clients that do not support it cannot read or write the table.

Limits

5. TIMESTAMP_NTZ

TIMESTAMP_NTZ holds the same fields, but all operations are performed without taking any time zone into account. It applies to Databricks SQL and Databricks Runtime 13.3 LTS and above, and the documentation marks it as Public Preview.

TIMESTAMP [ (p) ] WITHOUT TIME ZONE | TIMESTAMP_NTZ [ (p) ]

TIMESTAMP WITHOUT TIME ZONE applies to Databricks Runtime 17.1 and above. Precision works as for TIMESTAMP (6–9, default 6, 7–9 in Beta), with the same 1677–2262 limit when writing precision above 6 to Delta or Parquet and the same TIMESTAMP_NTZ(9) table limitations.

> SELECT current_timezone(), CAST(TIMESTAMP '2021-7-1T8:43:28' AS TIMESTAMP_NTZ);
  America/Los_Angeles 2021-07-01 08:43:28

Delta Lake support

• Support is enabled automatically when you create a Delta table with a TIMESTAMP_NTZ column, but not when you add a TIMESTAMP_NTZ column to an existing table. Enable it explicitly (this upgrades the table protocol):

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

• For TIMESTAMP_NTZ(9) on an existing table, enable both features:

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

Other limitations

• Photon support requires Databricks SQL or Databricks Runtime 15.4 and above.
• Not supported in OpenSharing in Databricks Runtime 14.0 and below.
• Supported in Delta, Parquet, ORC, Avro, JSON and CSV file sources, but schema inference from spark.read.csv(...) or spark.read.json(...) defaults to TIMESTAMP rather than TIMESTAMP_NTZ for backward compatibility.

References

• Databricks documentation: TIMESTAMP type
• Databricks documentation: TIMESTAMP_NTZ type
• Databricks documentation: SET TIME ZONE