TIMESTAMP_NTZ type

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 13.3 LTS and above

Represents values comprising values of fields year, month, day, hour, minute, and second. All operations are performed without taking any time zone into account.

This feature is in Public Preview. See the Notes section for unsupported features.

To use this feature on Delta Lake, you must enable support for the table. Feature support is enabled automatically when you create a new Delta table with a column of TIMESTAMP_NTZ type. It is not enabled automatically when you add a column of TIMESTAMP_NTZ type to an existing table. To enable support for TIMESTAMP_NTZ columns, support for the feature must be explicitly enabled for the existing table.

Enabling support upgrades your table protocol. See Delta Lake feature compatibility and protocols. The following command enables this feature:

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

Syntax

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

TIMESTAMP WITHOUT TIME ZONE applies to Databricks Runtime 17.1 and above.

  • 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_NTZ(p) with precision 7–9 is in Beta.

Limits

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

Note

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

Literals

TIMESTAMP_NTZ 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]' }
  • + 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_NTZ(9) literal.

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 the literal does not represent a proper timestamp Azure Databricks raises an error.

Notes

TIMESTAMP_NTZ has the following limitations:

  • Photon support requires Databricks SQL, or Databricks Runtime 15.4 and above.
  • Not supported in OpenSharing in Databricks Runtime 14.0 and below.
  • TIMESTAMP_NTZ type is supported in file sources including Delta/Parquet/ORC/AVRO/JSON/CSV. However, there is a limitation on the schema inference for JSON/CSV files with TIMESTAMP_NTZ columns. For backward compatibility, the default inferred timestamp type from spark.read.csv(...) or spark.read.json(...) will be TIMESTAMP type instead of TIMESTAMP_NTZ.

To use TIMESTAMP_NTZ(9) columns in an existing Delta Lake table, enable both the timestampNtz and timestampPrecision features:

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

Examples

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

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

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

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

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

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

> SELECT '1908-03-15 10:1:17'::TIMESTAMP WITHOUT TIME ZONE)
  1908-03-15 10:01:17

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

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

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

TIMESTAMP_NTZ(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