Comparing Time Zones in Databases: A Practical Guide to Avoiding Timestamp Pitfalls

Working with timestamps and time zones can be deceptively tricky, especially when dealing with databases that serve users across different geographical locations. If the phrase “Compare Time Zone” makes you slightly anxious, you’re not alone. Many developers, particularly those initially working in single time zone environments, can encounter unexpected issues when expanding to global applications.

(meme pretending that Gordon Ramsay says "I hate timezones in all timezones")

Initially, in localized settings, managing time might seem straightforward. For instance, in regions like Western Australia, the concept of time zones can feel less prominent in daily development. Systems might rely on simple DATE and TIMESTAMP data types, with the server time zone conveniently set to AUSTRALIA/Perth, seemingly resolving most immediate time-related concerns.

However, as systems grow to accommodate users globally – across the US, Africa, or various parts of Asia and Europe – the simplicity fades. To accurately handle time across borders, setting the server time zone to UTC (Coordinated Universal Time) becomes crucial. This necessitates using data types that are time zone aware, such as:

• TIMESTAMP WITH TIME ZONE: Essential for recording dates and times along with their specific time zone context.
• TIMESTAMP WITH LOCAL TIME ZONE: Useful for system audit trails or event logs where times should always be displayed in the user’s session time zone (typically UTC), regardless of the originating time zone.

The Problem: Implicit Time Zone Conversion in Comparisons

Consider a scenario where a business rule needs to validate appointment times against the current time. The rule dictates that if an appointment is scheduled for the previous day relative to the current time in the appointment’s time zone, an error message should be displayed, indicating it’s too late.

A seemingly straightforward piece of code to implement this might look like this:

IF appointment_time < TRUNC(current_time) THEN
  :p1_msg := 'This appointment was for the previous day and has expired.';
END IF;

The intention behind using TRUNC is to compare the appointment time against midnight of the current day in the relevant time zone. However, a test case failed unexpectedly, even when the surface values appeared correct:

appointment_time = 05-MAR-2021 07.00.00.000000 AM AUSTRALIA/Perth
current_time = 05-MAR-2021 06.45.00.000000 AM AUSTRALIA/Perth

Both times are clearly on the same day and in the same time zone (Australia/Perth), leading to the expectation that no error should be triggered. (Note: current_time is derived using localtimestamp at the time zone of the record being evaluated).

Initial checks confirmed that both appointment_time and current_time were indeed TIMESTAMP WITH TIME ZONE and held the values shown. Direct SQL queries were then used to investigate the logic:

SELECT
    TO_TIMESTAMP_TZ('05-MAR-2021 07.00.00.000000 AM AUSTRALIA/Perth') AS appt_time,
    TO_TIMESTAMP_TZ('05-MAR-2021 06.45.00.000000 AM AUSTRALIA/Perth') AS current_time
FROM DUAL;

-- APPT_TIME: 05-MAR-2021 07.00.00.000000000 AM AUSTRALIA/PERTH
-- CURRENT_TIME: 05-MAR-2021 06.45.00.000000000 AM AUSTRALIA/PERTH

A basic comparison without TRUNC works as expected:

WITH q AS (
    SELECT
        TO_TIMESTAMP_TZ('05-MAR-2021 07.00.00.000000 AM AUSTRALIA/Perth') AS appt_time,
        TO_TIMESTAMP_TZ('05-MAR-2021 06.45.00.000000 AM AUSTRALIA/Perth') AS current_time
    FROM DUAL
)
SELECT
    q.appt_time,
    q.current_time,
    CASE
        WHEN q.appt_time < q.current_time THEN 'FAIL'
        ELSE 'SUCCESS'
    END AS test
FROM q;

-- APPT_TIME: 05-MAR-2021 07.00.00.000000000 AM AUSTRALIA/PERTH
-- CURRENT_TIME: 05-MAR-2021 06.45.00.000000000 AM AUSTRALIA/PERTH
-- TEST: SUCCESS

However, incorporating TRUNC to mimic the failing code reveals the issue:

WITH q AS (
    SELECT
        TO_TIMESTAMP_TZ('05-MAR-2021 07.00.00.000000 AM AUSTRALIA/Perth') AS appt_time,
        TO_TIMESTAMP_TZ('05-MAR-2021 06.45.00.000000 AM AUSTRALIA/Perth') AS current_time
    FROM DUAL
)
SELECT
    q.appt_time,
    q.current_time,
    TRUNC(q.current_time),
    CASE
        WHEN q.appt_time < TRUNC(q.current_time) THEN 'FAIL'
        ELSE 'SUCCESS'
    END AS test
FROM q;

-- APPT_TIME: 05-MAR-2021 07.00.00.000000000 AM AUSTRALIA/PERTH
-- CURRENT_TIME: 05-MAR-2021 06.45.00.000000000 AM AUSTRALIA/PERTH
-- TRUNC(CURRENT_TIME): 03/05/2021
-- TEST: FAIL

The test unexpectedly fails! The problem arises from implicit data type conversion and time zone handling by Oracle. Initially, a suspicion of implicit conversion to DATE and potential UTC time zone conversion was considered:

WITH q AS (
    SELECT
        TO_TIMESTAMP_TZ('05-MAR-2021 07.00.00.000000 AM AUSTRALIA/Perth') AS appt_time,
        TO_TIMESTAMP_TZ('05-MAR-2021 06.45.00.000000 AM AUSTRALIA/Perth') AS current_time
    FROM DUAL
)
SELECT
    q.appt_time,
    q.current_time,
    CAST(q.appt_time AS DATE) AS cast_appt_date,
    CAST(q.current_time AS DATE) AS cast_current_date
FROM q;

-- APPT_TIME: 05-MAR-2021 07.00.00.000000000 AM AUSTRALIA/PERTH
-- CURRENT_TIME: 05-MAR-2021 06.45.00.000000000 AM AUSTRALIA/PERTH
-- CAST(APPT_TIME AS DATE): 03/05/2021 07:00:00 AM
-- CAST(CURRENT_TIME AS DATE): 03/05/2021 06:45:00 AM

Casting to DATE alone doesn’t reveal the issue, as both dates remain on the same day. The critical insight comes when considering how Oracle handles DATE to TIMESTAMP WITH TIME ZONE implicit conversion:

WITH q AS (
    SELECT
        TO_TIMESTAMP_TZ('05-MAR-2021 07.00.00.000000 AM AUSTRALIA/Perth') AS appt_time,
        TO_TIMESTAMP_TZ('05-MAR-2021 06.45.00.000000 AM AUSTRALIA/Perth') AS current_time
    FROM DUAL
)
SELECT
    q.appt_time,
    q.current_time,
    CAST(TRUNC(q.current_time) AS TIMESTAMP WITH TIME ZONE) AS trunc_current_time_tz,
    CASE
        WHEN q.appt_time < CAST(TRUNC(q.current_time) AS TIMESTAMP WITH TIME ZONE) THEN 'FAIL'
        ELSE 'SUCCESS'
    END AS test
FROM q;

-- APPT_TIME: 05-MAR-2021 07.00.00.000000000 AM AUSTRALIA/PERTH
-- CURRENT_TIME: 05-MAR-2021 06.45.00.000000000 AM AUSTRALIA/PERTH
-- CAST(TRUNC(CURRENT_TIME) AS TIMESTAMP WITH TIME ZONE): 05-MAR-2021 12.00.00.000000 AM +00:00
-- TEST: FAIL

Here, the root cause becomes clear. TRUNC on a TIMESTAMP WITH TIME ZONE first converts it to a DATE, stripping away the time zone information. When this DATE is implicitly converted back to TIMESTAMP WITH TIME ZONE for comparison, Oracle assigns the database’s default time zone, which is UTC in this scenario. Thus, TRUNC(current_time) becomes midnight UTC of that day. Comparing the original appointment_time (07:00 AM Australia/Perth) with midnight UTC leads to an incorrect “previous day” comparison.

The Solution: Explicitly Comparing Dates

To correctly compare just the dates, ensure both sides of the comparison are of DATE type, eliminating time zone considerations from the time component. The corrected logic involves explicitly casting appointment_time to DATE before comparison:

WITH q AS (
    SELECT
        TO_TIMESTAMP_TZ('05-MAR-2021 07.00.00.000000 AM AUSTRALIA/Perth') AS appt_time,
        TO_TIMESTAMP_TZ('05-MAR-2021 06.45.00.000000 AM AUSTRALIA/Perth') AS current_time
    FROM DUAL
)
SELECT
    q.appt_time,
    q.current_time,
    CASE
        WHEN CAST(q.appt_time AS DATE) < TRUNC(q.current_time) THEN 'FAIL'
        ELSE 'SUCCESS'
    END AS test
FROM q;

-- APPT_TIME: 05-MAR-2021 07.00.00.000000000 AM AUSTRALIA/PERTH
-- CURRENT_TIME: 05-MAR-2021 06.45.00.000000000 AM AUSTRALIA/PERTH
-- TEST: SUCCESS

The revised code becomes:

IF CAST(appointment_time AS DATE) < TRUNC(current_time) THEN
  :p1_msg := 'This appointment was for the previous day and has expired.';
END IF;

It’s worth noting that this issue might have gone unnoticed if the test data times were later in the day. Perth is UTC+8, so times before 8 AM Perth time are more likely to expose this kind of time zone related comparison problem when UTC is involved.

Key Takeaways for Time Zone Comparisons

Lesson #1: Time Zones Introduce Subtle Bugs. Systems handling timestamps and time zones are prone to subtle bugs that can easily slip through testing, especially if not tested with diverse time zone scenarios.

Lesson #2: Ensure Data Type Consistency in Comparisons. When comparing timestamps and dates across time zones, always verify that the data types are identical. If not, explicitly convert them to the desired type before performing the comparison to avoid unexpected implicit conversions and time zone discrepancies. This practice is crucial for reliable and accurate time-based logic in global applications.

By being mindful of these lessons, developers can more effectively “compare time zone” data in their databases and build robust, time zone-aware applications.