TIMESTAMP_NTZ
type
Applies to: 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 Databricks manage Delta Lake feature compatibility?. The following command enables this feature:
ALTER TABLE table_name SET TBLPROPERTIES ('delta.feature.timestampNtz' = 'supported')
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 Databricks raises an error.
Notes
TIMESTAMP_NTZ
has the following limitations:
Photon support requires Databricks SQL, or Databricks Runtime 15.4 and later.
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 fromspark.read.csv(...)
orspark.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