https://community.databricks.com/t5/technical-blog/introducing-timestamp-ntz-in-apache-spark-the-timestamp-without/ba-p/50586 <P><SPAN>by Gengliang Wang,&nbsp;Serge Rielau, Wenchen Fan, and Ivan Sadikov</SPAN></P> <H1><SPAN>Introduction</SPAN></H1> <P><SPAN>As the volume and variety of data grow, so does the need for refined data types to ensure consistency, accuracy, and efficiency. Time data is notoriously challenging due to the complexities associated with time zones. Apache Spark, in its pursuit to be the de-facto processing engine for big data, has recognized this challenge. In Spark 3.4, the community introduces the TIMESTAMP_NTZ type, a timestamp that operates without considering time zones. This blog post delves into the TIMESTAMP_NTZ type, its motivations, and how to make the most of it on Apache Spark and Databricks platform.</SPAN></P> <P>&nbsp;</P> <H1><SPAN>Why TIMESTAMP_NTZ?</SPAN></H1> <P><SPAN>Before the Apache Spark 3.4 release, the default and only timestamp type behaved similarly to Oracle's TIMESTAMP WITH LOCAL TIME ZONE, which is stored via normalizing to a standard time zone (UTC). However, Operating in multi-timezone environments or transferring data across systems presents unique challenges with timestamps. In these contexts, querying tables becomes intricate. Filtering based on timestamps requires not just an understanding of the data, but also an awareness of the source and target time zones. This added layer of complexity can make queries prone to errors and harder to maintain.&nbsp;</SPAN></P> <P><SPAN>Hence, there's a clear need for a timestamp type that remains consistent, and independent of the session's time zone. Enter TIMESTAMP_NTZ, which stands for "timestamp without time zone". This data type allows users to represent precise moments without the nuances of time zones.</SPAN></P> <P>&nbsp;</P> <H1><SPAN>Getting Started with TIMESTAMP_NTZ</SPAN></H1> <P><SPAN>Creating and manipulating tables/literals with the TIMESTAMP_NTZ type is simple. Here are some examples:</SPAN></P> <PRE><SPAN><FONT color="#FF0000">CREATE TABLE</FONT> t(ts <FONT color="#FF0000">TIMESTAMP_NTZ</FONT>) using parquet;<BR /><FONT color="#FF0000">ALTER TABLE</FONT> t <FONT color="#FF0000">ADD COLUMN</FONT> (ts2 <FONT color="#FF0000">TIMESTAMP_NTZ</FONT>);<BR /><FONT color="#FF0000">SELECT CAST</FONT>('<FONT color="#333399">2021-07-07 00:00:00</FONT>' <FONT color="#FF0000">AS</FONT> <FONT color="#FF0000">TIMESTAMP_NTZ</FONT>);<BR /><FONT color="#FF0000">SELECT</FONT> <FONT color="#FF0000">TIMESTAMP_NTZ</FONT>'<FONT color="#333399">2021-07-07 00:00:00</FONT>';</SPAN></PRE> <P><SPAN>A standout feature introduced in Spark 3.4 is the </SPAN><A href="https://docs.databricks.com/en/sql/language-manual/functions/convert_timezone.html" target="_blank" rel="noopener"><SPAN>convert_timezone()</SPAN></A><SPAN> function. This function aids users in converting between time zones for TIMESTAMP_NTZ:</SPAN></P> <TABLE> <TBODY> <TR> <TD> <PRE><FONT color="#808080"><I><SPAN>-- Convert from 'America/Los_Angeles' to 'UTC'</SPAN></I></FONT><SPAN><BR /></SPAN><FONT color="#FF0000">SELECT</FONT><SPAN> convert_timezone(</SPAN><SPAN>'<FONT color="#333399">America/Los_Angeles</FONT>'</SPAN><SPAN>, </SPAN><SPAN>'UTC'</SPAN><SPAN>, <FONT color="#FF0000">TIMESTAMP_NTZ</FONT></SPAN><SPAN>'<FONT color="#333399">2021-12-06 00:00:00</FONT>'</SPAN><SPAN>);</SPAN><SPAN><BR /></SPAN><FONT color="#808080"><I><SPAN>-- Output: 2021-12-06 08:00:00</SPAN></I></FONT></PRE> </TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <H1><SPAN>TIMESTAMP_NTZ vs. TIMESTAMP</SPAN></H1> <P><SPAN>Given the following table&nbsp;<STRONG>recorded_times</STRONG>:</SPAN></P> <TABLE> <TBODY> <TR> <TD> <PRE><FONT color="#FF0000">SET TIME ZONE</FONT> <SPAN>'<FONT color="#333399">America/Los_Angeles</FONT>'</SPAN><SPAN>;</SPAN><SPAN><BR /></SPAN><FONT color="#FF0000">CREATE TABLE</FONT><SPAN> recorded_times (ts </SPAN><FONT color="#FF0000">TIMESTAMP</FONT><SPAN>, ts_ntz </SPAN><FONT color="#FF0000">TIMESTAMP_NTZ</FONT><SPAN>) </SPAN><FONT color="#FF0000">USING</FONT><SPAN> parquet;</SPAN><SPAN><BR /></SPAN><FONT color="#FF0000">INSERT INTO</FONT><SPAN> recorded_times </SPAN><FONT color="#FF0000">VALUES</FONT><SPAN> (</SPAN><STRONG>now</STRONG><SPAN>(), </SPAN><STRONG>now</STRONG><SPAN>());</SPAN></PRE> </TD> </TR> </TBODY> </TABLE> <P><SPAN>In this 'America/Los_Angeles' time zone, both TIMESTAMP and TIMESTAMP_NTZ capture the same time, say </SPAN><STRONG>2023-10-05 17:00:00</STRONG><SPAN>.</SPAN></P> <P><SPAN>However, when we switch our session to another time zone:</SPAN></P> <PRE><SPAN><FONT color="#FF0000">SET TIME ZONE</FONT> '<FONT color="#333399">America/New_York</FONT>';<BR /><FONT color="#FF0000">SELECT</FONT> * <FONT color="#FF0000">FROM</FONT> recorded_times;</SPAN></PRE> <P><SPAN>The TIMESTAMP value adjusts to reflect the new time zone</SPAN><SPAN>, while TIMESTAMP_NTZ remains consistent:</SPAN></P> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="33.333333333333336%" height="30px">Column type</TD> <TD width="33.333333333333336%" height="30px">In 'America/Los_Angeles'&nbsp;Time Zone</TD> <TD width="33.333333333333336%" height="30px">In 'America/New_York' Time Zone</TD> </TR> <TR> <TD width="33.333333333333336%" height="30px">TIMESTAMP</TD> <TD width="33.333333333333336%" height="30px">2023-10-05 17:00:00</TD> <TD width="33.333333333333336%" height="30px"><STRONG>2023-10-05 20:00:00</STRONG></TD> </TR> <TR> <TD width="33.333333333333336%" height="30px">TIMESTAMP_NTZ</TD> <TD width="33.333333333333336%" height="30px">2023-10-05 17:00:00</TD> <TD width="33.333333333333336%" height="30px"><STRONG>2023-10-05 17:00:00</STRONG></TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <H1><SPAN>Interoperability and Support</SPAN></H1> <P><SPAN>The TIMESTAMP_NTZ type offers seamless conversion with Date, Timestamp, and String types. It's supported across Python, SQL, Scala, and Java in Spark. Moreover, it accommodates file sources such as Delta, Parquet, ORC, Avro, JSON, and CSV and ensures compatibility with Hive metastore and </SPAN><A href="https://www.databricks.com/product/unity-catalog" target="_blank" rel="noopener"><SPAN>Unity Catalog</SPAN></A><SPAN>.</SPAN></P> <P>&nbsp;</P> <H1><SPAN>Wrapping Up</SPAN></H1> <P><SPAN>The introduction of the TIMESTAMP_NTZ feature in Spark SQL highlights Spark's commitment to addressing the evolving needs of its users. If you're grappling with timestamp data and want to sidestep the complications of time zones, TIMESTAMP_NTZ is your go-to feature.</SPAN></P> Tue, 21 Nov 2023 05:19:39 GMT GengliangWang 2023-11-21T05:19:39Z https://community.databricks.com/t5/technical-blog/introducing-timestamp-ntz-in-apache-spark-the-timestamp-without/ba-p/50586 <P><SPAN>Introducing TIMESTAMP_NTZ in Apache Spark: Understand its significance in the Spark ecosystem, and how it offers a consistent approach to timestamp data, eliminating multiple time zone hassles.&nbsp;</SPAN></P> Tue, 21 Nov 2023 05:19:39 GMT https://community.databricks.com/t5/technical-blog/introducing-timestamp-ntz-in-apache-spark-the-timestamp-without/ba-p/50586 GengliangWang 2023-11-21T05:19:39Z https://community.databricks.com/t5/technical-blog/introducing-timestamp-ntz-in-apache-spark-the-timestamp-without/bc-p/124070#M681 <P>Thanks for adding TIMESTAMP_NTZ timestamp.</P><P>It will be helpful if you add TIMESTAMP_TZ too which will store and display the time with original timezone.</P><P>Currently using TIMESTAMP data type, it is not possible to know the original timezone of that time.&nbsp;</P><P>For example: the timestamp value, '2025-01-01T01:10:10-07:00' which is UTC -7 timezone, if we store this as TIMESTAMP then during query it will convert to system's local timezone (for example UCT-5) and display as&nbsp;'2025-01-01T03:10:10-05:00'. So we don't know what is the original timezone.&nbsp;</P> Sat, 05 Jul 2025 04:08:20 GMT https://community.databricks.com/t5/technical-blog/introducing-timestamp-ntz-in-apache-spark-the-timestamp-without/bc-p/124070#M681 SN 2025-07-05T04:08:20Z https://community.databricks.com/t5/technical-blog/introducing-timestamp-ntz-in-apache-spark-the-timestamp-without/bc-p/124071#M682 <P>This is a nice feature - time zones / date times are almost always a headache. Thanks for the write up!</P> Sat, 05 Jul 2025 06:40:28 GMT https://community.databricks.com/t5/technical-blog/introducing-timestamp-ntz-in-apache-spark-the-timestamp-without/bc-p/124071#M682 TheOC 2025-07-05T06:40:28Z