Database Timestamp Differences: A Deep Dive into Time Zones and Data Integrity

Databases often store date and time information, but the variety of data types available for this purpose can be confusing. Understanding the nuances of these types, especially concerning time zones, is crucial for accurate data analysis and application behavior. This article explores different date and time types, focusing on Snowflake’s default TIMESTAMP_NTZ, and provides practical test cases to illustrate potential pitfalls and best practices.

Why Different Date Time Types?

Different date and time types exist to cater to varying requirements regarding precision, storage size, and time zone awareness. Here’s a breakdown:

• DATE: Stores only the date portion (year, month, day). Suitable for representing birthdays, anniversaries, or events that only require date information.
• TIME: Stores only the time portion (hour, minute, second, fractional seconds). Useful for recording meeting times or shift schedules.
• DATETIME (or TIMESTAMP without Time Zone): Stores both date and time. Crucially, it does not store any time zone information. This means the time value is interpreted as is, without reference to any specific time zone. Snowflake’s TIMESTAMP_NTZ falls into this category.
• TIMESTAMP WITH TIME ZONE: Stores both date and time, along with time zone information. This allows the database to understand the origin of the timestamp and perform accurate conversions. Snowflake’s TIMESTAMP_TZ falls into this category. The timezone is not stored explicitly in the column but stored as an offset from UTC.
• TIMESTAMP WITH LOCAL TIME ZONE: Similar to TIMESTAMP WITH TIME ZONE, but automatically converts the timestamp to the session’s time zone when retrieved. Snowflake’s TIMESTAMP_LTZ falls into this category. The timezone is not stored explicitly in the column but stored as an offset from UTC.

When to use which type:

• Use DATE when you only need date information.
• Use TIME when you only need time information.
• Use TIMESTAMP_NTZ (or DATETIME) when you need date and time, and the time zone is irrelevant, already understood, or handled separately by the application. Good for capturing order of events that occur in a system where the time zone is already known.
• Use TIMESTAMP_TZ when you need to know the original time zone in which the event occurred. This is critical for applications dealing with globally distributed users or systems.
• Use TIMESTAMP_LTZ when you want the database to automatically convert timestamps to the session’s time zone for display or processing.

Why TIMESTAMP_NTZ as Snowflake’s Default?

The choice of TIMESTAMP_NTZ as the default in Snowflake stems from several factors:

• Simplicity and Performance: TIMESTAMP_NTZ avoids the overhead associated with time zone calculations and conversions, leading to potentially faster query performance.
• Control and Flexibility: It gives the user (or application) complete control over time zone handling. If the application consistently handles time zones or stores the time zone information separately, using TIMESTAMP_NTZ can be more efficient.
• Historical Compatibility: Many existing systems and data pipelines use DATETIME (without time zone) and assuming a specific time zone implicitly.

However, using TIMESTAMP_NTZ without careful consideration can lead to issues, as demonstrated in the test cases below. It’s crucial to understand the implications of not storing time zone information and to implement a consistent strategy for time zone handling.

Caveats of Working with Date and Time Types

Working with date and time data, especially when time zones are involved, presents several challenges:

• Ambiguity: TIMESTAMP_NTZ values are ambiguous without knowing the intended time zone. The same value could represent different moments in time depending on the time zone.
• Daylight Saving Time (DST): DST transitions can cause timestamps to be skipped or repeated, leading to inconsistencies if not handled correctly.
• Time Zone Database Updates: Time zone rules are constantly updated to reflect political and legal changes. Using outdated time zone information can result in inaccurate conversions.
• Data Integration Issues: When integrating data from different systems with different time zone conventions, careful conversion and alignment are necessary.
• Query Performance: Complex time zone conversions in queries can significantly impact performance.

Snowflake Timestamp Test Cases

Let’s explore these caveats with practical Snowflake examples. These examples showcase the behavior of different timestamp types and highlight the importance of proper time zone management.

Before we begin let’s setup the environment

-- Setting the timezone for the session
ALTER SESSION SET timezone = 'America/Los_Angeles';
-- Validating that the TimeZone has been set
SHOW PARAMETERS LIKE 'timezone' IN SESSION;
-- Create temporary table to run tests
CREATE OR REPLACE TEMPORARY TABLE timestamp_tests (
id INT,
ntz_ts TIMESTAMP_NTZ,
tz_ts TIMESTAMP_TZ,
ltz_ts TIMESTAMP_LTZ,
notes VARCHAR(255)
);
-- truncate the table
TRUNCATE TABLE timestamp_tests;

Scenario 1: Date Math Across Time Zones

This scenario demonstrates how date math (adding or subtracting time) can shift a date into a different time zone region.
-- Insert a timestamp representing 10:00 AM PST on March 10, 2024 (after DST started in US Pacific)
INSERT INTO timestamp_tests (id, ntz_ts, tz_ts, ltz_ts, notes) VALUES (1, '2024-03-10 10:00:00', '2024-03-10 10:00:00 PST', '2024-03-10 10:00:00 PST', 'Original PST timestamp');
-- Add 14 hours to the NTZ timestamp
INSERT INTO timestamp_tests (id, ntz_ts, notes)
SELECT 2, DATEADD(hour, 14, ntz_ts), 'NTZ + 14 hours'
FROM timestamp_tests WHERE id = 1;
-- Add 14 hours to the TZ timestamp
INSERT INTO timestamp_tests (id, tz_ts, notes)
SELECT 3, DATEADD(hour, 14, tz_ts), 'TZ + 14 hours'
FROM timestamp_tests WHERE id = 1;
-- Add 14 hours to the LTZ timestamp
INSERT INTO timestamp_tests (id, ltz_ts, notes)
SELECT 4, DATEADD(hour, 14, ltz_ts), 'LTZ + 14 hours'
FROM timestamp_tests WHERE id = 1;
--Display the data
SELECT id, ntz_ts, tz_ts, ltz_ts, notes FROM timestamp_tests ORDER BY id;

Expected Result and Explanation:

• NTZ + 14 hours: 2024-03-11 00:00:00.000 – The NTZ timestamp is treated as a raw date and time value. Adding 14 hours simply adds 14 hours.
• TZ + 14 hours: 2024-03-11 00:00:00.000 +0000 – The database knows the original timestamp was in PST and converts the timestamp to be equivalent to PST + 14 hours
• LTZ + 14 hours: 2024-03-10 17:00:00.000 -0700 – The database knows the original timestamp was in PST, adds the time, converts to UTC and then converts back to the current session’s time zone which is PST

Conclusion: Adding time to TIMESTAMP_NTZ simply increments the raw date and time value, without considering time zone transitions. Adding time to TIMESTAMP_TZ and TIMESTAMP_LTZ considers the time zone associated with the timestamp and the result is adjusted accordingly. TIMESTAMP_LTZ returns the result in your current session’s timezone.

Scenario 2: Date Time Match Across Time Zones

This scenario tests how different timestamp types behave when comparing timestamps originating from different time zones.

-- Insert a timestamp representing 9:00 AM EST (New York) on March 10, 2024 (after DST started) 
INSERT INTO timestamp_tests (id, ntz_ts, tz_ts, ltz_ts, notes) VALUES (5, '2024-03-10 09:00:00', '2024-03-10 09:00:00 EST', '2024-03-10 09:00:00 EST', 'New York EST timestamp'); 
-- Attempt to find the record where the NTZ timestamp matches 9:00 AM PST
SELECT * FROM timestamp_tests WHERE ntz_ts = '2024-03-10 09:00:00';
 -- Attempt to find the record where the TZ timestamp matches 9:00 AM PST 
SELECT * FROM timestamp_tests WHERE tz_ts = '2024-03-10 09:00:00 PST';
 -- Attempt to find the record where the LTZ timestamp matches 9:00 AM PST
SELECT * FROM timestamp_tests WHERE ltz_ts = '2024-03-10 09:00:00 PST'; 
-- Attempt to find the record where the TZ timestamp matches 9:00 AM EST 
SELECT * FROM timestamp_tests WHERE tz_ts = '2024-03-10 09:00:00 EST'; 
-- Attempt to find the record where the LTZ timestamp matches 9:00 AM EST
SELECT * FROM timestamp_tests WHERE ltz_ts = '2024-03-10 09:00:00 EST';

Expected Result and Explanation:

• NTZ Match (PST): No match will be found. The TIMESTAMP_NTZ comparison is done on raw values. 9:00 AM EST is not equal to 9:00 AM PST.
• TZ Match (PST): No match will be found. When using TIMESTAMP_TZ to compare you need to use the correct timezone. If you use PST and the timezone is EST it will not be found
• LTZ Match (PST): The record WILL be found. The database converts both timestamps to UTC and compares them. Since the timestamps represent the same moment in time, they match when normalized to UTC, which then is converted back to the Session’s time zone.
• TZ Match (EST): The record WILL be found. The database converts both timestamps to UTC and compares them. Since the timestamps represent the same moment in time, they match when normalized to UTC.
• LTZ Match (EST): The record WILL be found. The database converts both timestamps to UTC and compares them. Since the timestamps represent the same moment in time, they match when normalized to UTC, which then is converted back to the Session’s time zone.

Conclusion: When comparing timestamps, TIMESTAMP_NTZ compares the raw date and time values, ignoring time zones. TIMESTAMP_TZ and TIMESTAMP_LTZ perform comparisons based on the equivalent UTC time. TIMESTAMP_LTZ converts the values to the current session’s timezone after comparison.

Scenario 3: DST Inserts and Reads

This scenario tests how TIMESTAMP_NTZ, TIMESTAMP_TZ, and TIMESTAMP_LTZ handle inserts during daylight saving time in one time zone and reads in another time zone that is not yet in daylight saving time.-- Switch Session TimeZone to Europe/Berlin. Berlin DST starts March 31, 2024
 ALTER SESSION SET timezone = 'Europe/Berlin';
-- Insert a timestamp representing 9:00 AM EST (New York) on March 24, 2024 (before Berlin DST)
INSERT INTO timestamp_tests (id, ntz_ts, tz_ts, ltz_ts, notes) VALUES (6, '2024-03-24 09:00:00', '2024-03-24 09:00:00 EST', '2024-03-24 09:00:00 EST', 'EST timestamp before Berlin DST');
-- Read the timestamp values in Berlin time.
SELECT id, ntz_ts, tz_ts, ltz_ts, notes FROM timestamp_tests WHERE id = 6;

Expected Result and Explanation:

• ntz\_ts: 2024-03-24 09:00:00.000 – The raw value is displayed as is, without any time zone conversion. It’s still interpreted as 9:00 AM, but now in the context of Berlin time (which is incorrect).
• tz\_ts: 2024-03-24 14:00:00.000 +0000 – The database knows the original timestamp was in EST and converts the timestamp to UTC so the timestamp in Berlin will be 2 PM
• ltz\_ts: 2024-03-24 15:00:00.000 +0100 – The database knows the original timestamp was in EST, converts to UTC, and then converts to Berlin time (which is UTC+1 because Berlin is not yet in DST).

Conclusion: TIMESTAMP_NTZ does not account for time zones and simply displays the raw value. TIMESTAMP_TZ correctly converts the timestamp to UTC, allowing for accurate representation in different time zones. TIMESTAMP_LTZ converts to UTC then to the session’s timezone, which is helpful if you want to work in that session’s time zone.

Scenario 4: Inserts and Reads Across Time Zones

This scenario tests inserting a timestamp in one time zone and reading it in another.-- Switch Session TimeZone back to America/Los_Angeles
ALTER SESSION SET timezone = 'America/Los_Angeles';
-- Insert a timestamp representing 14:00 PM CET (Central European Time) on March 24, 2024
INSERT INTO timestamp_tests (id, ntz_ts, tz_ts, ltz_ts, notes) VALUES (7, '2024-03-24 14:00:00', '2024-03-24 14:00:00 CET', '2024-03-24 14:00:00 CET', 'CET timestamp');
-- Read the timestamp values in Los Angeles time.
SELECT id, ntz_ts, tz_ts, ltz_ts, notes FROM timestamp_tests WHERE id = 7;

Expected Result and Explanation:

• ntz\_ts: 2024-03-24 14:00:00.000 – The raw value is displayed as is, without any time zone conversion. It’s still interpreted as 2:00 PM, but now in the context of Los Angeles time (which is incorrect).
• tz\_ts: 2024-03-24 13:00:00.000 +0000 – The database knows the original timestamp was in CET and converts the timestamp to UTC to be equivalent to 1 PM UTC.
• ltz\_ts: 2024-03-24 06:00:00.000 -0700 – The database knows the original timestamp was in CET, converts to UTC, and then converts to Los Angeles time (which is UTC-7 due to PST).

Conclusion: As before, TIMESTAMP_NTZ simply displays the raw value. TIMESTAMP_TZ correctly converts the timestamp to UTC, maintaining its true representation. TIMESTAMP_LTZ provides the timestamp in the session’s local time zone.

Scenario 5: Consistent UTC Conversion

This scenario demonstrates how TIMESTAMP_TZ ensures consistent UTC conversion even when inserting timestamps from different time zones that represent the same moment in time.-- Insert 4:00 PM in Bulgarian Time (BG)
INSERT INTO timestamp_tests (id, tz_ts, notes) VALUES (8, '2024-03-24 16:00:00 Europe/Sofia', '4 PM BG time');
-- Insert 9:00 AM in New York Time (NY) on the same date
INSERT INTO timestamp_tests (id, tz_ts, notes) VALUES (9, '2024-03-24 09:00:00 America/New_York', '9 AM NY time');
-- Compare the UTC representations of the two timestamps
SELECT id, tz_ts, convert_timezone('UTC', tz_ts) AS utc_ts, notes FROM timestamp_tests WHERE id IN (8, 9);

Expected Result and Explanation:

The utc_ts column will show the same UTC timestamp value for both rows, despite the different input time zones. This demonstrates that TIMESTAMP_TZ correctly stores and converts timestamps to UTC. For 4 PM BG time the UTC Timestamp is ‘2024-03-24 14:00:00.000’, for 9 AM NY time the UTC Timestamp is ‘2024-03-24 13:00:00.000’

Conclusion: TIMESTAMP_TZ provides a reliable way to store and retrieve timestamps in UTC, ensuring consistency across different time zones. It is important to note that the session timezone will affect what it displays to the user.

Choosing the Right Timestamp Type

The best timestamp type for your needs depends on your specific application requirements:

• TIMESTAMP_NTZ: Use if time zone is irrelevant, already handled externally, or for simple ordering within a single, well-defined time zone. Be very cautious if dealing with data from different time zones or if DST transitions are a concern.
• TIMESTAMP_TZ: Use when you need to preserve the original time zone information. This is critical for applications dealing with global users or systems where the original time zone is important for analysis and accurate interpretation.
• TIMESTAMP_LTZ: Use when you want the database to automatically convert timestamps to the session’s time zone. This simplifies display and processing within a specific user’s context.

Recommendations:

• Favor TIMESTAMP_TZ or TIMESTAMP_LTZ: Unless you have a very specific reason to use TIMESTAMP_NTZ, consider using TIMESTAMP_TZ or TIMESTAMP_LTZ. They provide greater accuracy and flexibility in dealing with time zones.
• Be Explicit: Always specify the time zone when inserting or querying TIMESTAMP_TZ data. Avoid relying on implicit time zone assumptions.
• Use UTC as the Source of Truth: When possible, convert all incoming timestamps to UTC before storing them in the database. This simplifies time zone conversions and avoids potential inconsistencies.
• Test Thoroughly: Thoroughly test your application’s time zone handling, especially around DST transitions, to ensure data integrity and correct behavior.

Conclusion

Understanding the differences between database timestamp types is critical for ensuring data accuracy and preventing unexpected behavior. TIMESTAMP_NTZ offers simplicity but can lead to issues if time zones are not handled correctly. TIMESTAMP_TZ provides the most robust solution for handling time zones, ensuring consistent and accurate data representation. By carefully considering your application’s requirements and choosing the appropriate timestamp type, you can build more reliable and globally aware applications. Remember to test thoroughly and always be mindful of the complexities of time zones and daylight saving time.