21 – Managing Data in Different Time Zones (Or: Your Timestamps Are Lying to You)

And look, storing dates without time zones is like scheduling a global meeting and only writing “9 AM” in the invite. Whose 9 AM? Where? On which planet? This lesson is about not doing that.

After this chapter you should be able to:

• Use datetime data types that store fractional seconds and time zones
• Store the difference between two datetime values using INTERVAL types
• Use key datetime functions like CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP, EXTRACT, FROM_TZ, and the various INTERVAL helpers

1. Session Time Zone vs Database Time Zone

Scenario:

• Rick is in Australia happily ordering from “OracleKart”.
• The servers are in the US.
• The order timestamp needs to reflect Rick’s local time, not “whatever time the server happens to be on.”

Key idea: Oracle maintains:

• A database time zone – where the database lives
• A session time zone – where you live (or at least where your client pretends you live)

You can set the session time zone:

ALTER SESSION SET TIME_ZONE = '-07:00';  -- absolute offset
    ALTER SESSION SET TIME_ZONE = DBTIMEZONE;
    ALTER SESSION SET TIME_ZONE = LOCAL;
    ALTER SESSION SET TIME_ZONE = 'Australia/Sydney';  -- named region

Then inspect:

SELECT dbtimezone, sessiontimezone
    FROM   dual;

Database time zone = server’s reality. Session time zone = user’s reality. You need both.

2. SYSDATE vs CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP

This is where people get confused and blame Oracle when it was really their choice of function all along.

• SYSDATE
• Type: DATE
• Source: database server clock (no session time zone involved)
• CURRENT_DATE
• Type: DATE
• Source: user session time zone
• CURRENT_TIMESTAMP
• Type: TIMESTAMP WITH TIME ZONE
• Source: user session time zone, with fractional seconds + zone
• LOCALTIMESTAMP
• Type: TIMESTAMP
• Source: user session time zone, without time zone field

Example:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
    ALTER SESSION SET TIME_ZONE = '-07:00';
    
    SELECT SYSDATE          AS sysdate,
           CURRENT_DATE     AS current_date,
           CURRENT_TIMESTAMP,
           LOCALTIMESTAMP
    FROM   dual;

Change the session time zone:

ALTER SESSION SET TIME_ZONE = '+08:00';

Run the same SELECT again:

• SYSDATE stays tied to the server’s time zone
• CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP jump to the new session time zone

Which is exactly what you want when Rick in Sydney is talking to a database in California.

3. Datetime Data Types: Beyond DATE

3.1 TIMESTAMP

Fields:

• YEAR, MONTH, DAY
• HOUR, MINUTE, SECOND
• Fractional seconds (up to 9 digits)

Example:

TIMESTAMP '2016-03-06 11:00:00.123456'

3.2 TIMESTAMP WITH TIME ZONE

All of TIMESTAMP plus:

• TIMEZONE_HOUR, TIMEZONE_MINUTE
• Or TIMEZONE_REGION (e.g. Europe/London)

Example:

TIMESTAMP '2016-03-06 11:00:00.123456 -08:00'

3.3 TIMESTAMP WITH LOCAL TIME ZONE

• Internally stored in the database time zone
• Automatically converted to the session time zone on SELECT
• Displays like a TIMESTAMP, but values are “localised” per session

This is great for “always show this time in the user’s local zone” semantics.

4. Example: WEB_ORDERS with Timed Delivery

Imagine an online store:

CREATE TABLE web_orders (
      order_id      NUMBER,
      order_date    TIMESTAMP WITH TIME ZONE,
      delivery_time TIMESTAMP WITH LOCAL TIME ZONE
    );

Insert using current session’s date/time:

INSERT INTO web_orders (order_id, order_date, delivery_time)
    VALUES (
      1,
      CURRENT_TIMESTAMP,
      CURRENT_TIMESTAMP + INTERVAL '2' DAY  -- deliver in two days
    );

Query:

SELECT order_id,
           order_date,
           delivery_time
    FROM   web_orders;

order_date shows the exact instant + time zone. delivery_time is stored in database time zone, but displays in your session time zone.

5. INTERVAL Data Types: Storing Differences, Not Points

Two families:

• INTERVAL YEAR TO MONTH – year/month differences
• INTERVAL DAY TO SECOND – day/time differences (with fractional seconds)

5.1 INTERVAL YEAR TO MONTH

Example table:

CREATE TABLE warranty (
      product_id    NUMBER,
      warranty_time INTERVAL YEAR(3) TO MONTH
    );

Insert:

INSERT INTO warranty VALUES (123, INTERVAL '8' MONTH);
    INSERT INTO warranty VALUES (155, INTERVAL '200' YEAR(3));
    INSERT INTO warranty VALUES (678, INTERVAL '200-11' YEAR(3) TO MONTH);

Result:

SELECT * FROM warranty;
    -- 123  +00-08
    -- 155  +200-00
    -- 678  +200-11

5.2 INTERVAL DAY TO SECOND

Example table:

CREATE TABLE lab (
      experiment_id NUMBER,
      test_time     INTERVAL DAY(2) TO SECOND
    );

Insert:

INSERT INTO lab VALUES (100012, INTERVAL '90' DAY);
    INSERT INTO lab VALUES (56098, INTERVAL '6 03:30:16' DAY TO SECOND);

Result:

SELECT * FROM lab;
    -- 100012  +90 00:00:00
    -- 56098   +06 03:30:16

These types are ideal when you care about “how long” rather than “what exact timestamp.”

6. Useful Datetime Functions

6.1 EXTRACT

Pull a specific field (year, month, etc.) from a datetime:

SELECT last_name,
           hire_date,
           EXTRACT(YEAR  FROM hire_date) AS hire_year,
           EXTRACT(MONTH FROM hire_date) AS hire_month
    FROM   employees;

Filter by year:

SELECT last_name, hire_date
    FROM   employees
    WHERE  EXTRACT(YEAR FROM hire_date) > 2007;

6.2 SESSIONTIMEZONE, DBTIMEZONE, and TZ_OFFSET

Find out what your session thinks the time zone is:

SELECT sessiontimezone, dbtimezone
    FROM   dual;

Get offset for a named region:

SELECT tz_offset('US/Eastern')    AS us_eastern,
           tz_offset('Canada/Yukon')  AS canada_yukon,
           tz_offset('Europe/London') AS london
    FROM   dual;

6.3 FROM_TZ and TO_TIMESTAMP

Convert a local timestamp to TIMESTAMP WITH TIME ZONE:

SELECT FROM_TZ(
             TO_TIMESTAMP('06-MAR-2016 11:00:00',
                          'DD-MON-YYYY HH24:MI:SS'),
             'Australia/North'
           ) AS aussie_time
    FROM   dual;

Or just parse string → timestamp:

SELECT TO_TIMESTAMP('06-MAR-2016 11:00:00',
                        'DD-MON-YYYY HH24:MI:SS') AS ts
    FROM   dual;

6.4 TO_YMINTERVAL and TO_DSINTERVAL

Apply year/month or day/second offsets:

-- Add 1 year 2 months
    SELECT hire_date,
           hire_date + TO_YMINTERVAL('01-02') AS plus_1y2m
    FROM   employees;
    
    -- Add 100 days 10 hours
    SELECT hire_date,
           hire_date + TO_DSINTERVAL('100 10:00:00') AS plus_100d10h
    FROM   employees;

These functions are much clearer (and less error‑prone) than trying to remember “how many days is 18 months again?”

7. Daylight Saving Time (The Part Where Everything Gets Weird)

Daylight saving time introduces two kinds of special moments:

• Spring forward:
• Time jumps from 01:59:59 to 03:00:00.
• Times from 02:00:00 to 02:59:59 do not exist.
• Fall back:
• Time jumps from 02:00:00 back to 01:00:01.
• Times between 01:00:01 and 02:00:00 are ambiguous (you see them twice).

If you store proper TIMESTAMP WITH TIME ZONE values, Oracle can distinguish between those two different 1:30 AMs. If you just use DATE with no zone, good luck.

What You Should Be Able To Do Now

By the end of this lesson you should be able to:

• Choose between SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP without guessing
• Use TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE appropriately
• Store and use INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND values
• Use functions like EXTRACT, TZ_OFFSET, FROM_TZ, TO_TIMESTAMP, TO_YMINTERVAL, and TO_DSINTERVAL in real queries
• Reason about how daylight saving and time zones affect stored and displayed dates

In short, you can now timestamp things in a way that survives crossing time zones, DST changes, and users who insist on working from anywhere with decent Wi‑Fi. Which, given the state of the world, is pretty much everyone.