Handling Time Zones Correctly in Oracle and APEX

Time Zone Bugs Are Subtle and Expensive

Applications that serve users across time zones must handle date and time values carefully. A meeting scheduled at “3:00 PM” means different moments in time depending on whether the user is in New York, London, or Tokyo. Oracle provides robust time zone support, but using it correctly requires understanding the difference between DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE data types.

Oracle Date and Timestamp Types

DATE stores a date and time with no time zone information. TIMESTAMP adds fractional seconds but still has no time zone. TIMESTAMP WITH TIME ZONE stores the time zone offset or region name with the value. TIMESTAMP WITH LOCAL TIME ZONE stores the value in the database time zone and converts to the session time zone on retrieval.

-- TIMESTAMP WITH TIME ZONE: explicit
CREATE TABLE meetings (
    meeting_id    NUMBER PRIMARY KEY,
    meeting_time  TIMESTAMP WITH TIME ZONE,
    description   VARCHAR2(200)
);

INSERT INTO meetings VALUES (1,
    TIMESTAMP '2025-06-15 15:00:00 America/New_York',
    'Q3 Planning');

-- Query converts to the session time zone
ALTER SESSION SET TIME_ZONE = 'Europe/London';
SELECT meeting_id, meeting_time,
       meeting_time AT TIME ZONE 'Asia/Tokyo' AS tokyo_time
  FROM meetings;

Best Practice: Store in UTC, Display in Local Time

The simplest approach for multi-timezone applications is to store all timestamps in UTC and convert to the user’s local time zone for display:

-- Store in UTC
INSERT INTO events (event_time) VALUES (SYS_EXTRACT_UTC(SYSTIMESTAMP));

-- Display in user's time zone
SELECT event_id,
       FROM_TZ(CAST(event_time AS TIMESTAMP), 'UTC')
           AT TIME ZONE :G_USER_TIMEZONE AS local_time
  FROM events;

APEX and Time Zones

APEX stores session state values as strings, which means DATE and TIMESTAMP values are converted using the application’s date format mask. To handle time zones in APEX, store the user’s preferred time zone in an application item (loaded from a preferences table on session creation), and use it in SQL queries and PL/SQL to convert stored UTC times to local display times.

For date picker items, be aware that the browser sends the date value in the user’s local time but APEX processes it on the server. If the server and browser are in different time zones, the time portion may shift during the round trip. Using TIMESTAMP WITH TIME ZONE columns and explicit time zone conversions eliminates this class of bugs.

Common Pitfalls

Never compare DATE columns with TIMESTAMP WITH TIME ZONE values without explicit conversion. Never assume SYSDATE returns UTC (it returns the database server’s OS time zone). Never store time zone offsets as fixed numbers like “+05:00” because they do not account for daylight saving time; use region names like ‘America/New_York’ instead.