`TIMESTAMP_NTZ` type

Article
09/26/2024

Applies to: check marked yes Databricks SQL 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 How does Azure Databricks manage Delta Lake feature compatibility?. The following command enables this feature:

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

Syntax

TIMESTAMP_NTZ

Limits

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

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

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

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:

Not supported in Photon.
Not supported in Databricks data sources used in Power BI, Tableau, Databricks JDBC/ODBC drivers, or Databricks open source drivers.
Not supported in Delta Sharing 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.

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 TIMESTAMP_NTZ'+10000';
  +10000-01-01 00:00:00

TIMESTAMP_NTZ type