16.3 Time Zone Functions & Session Environment

16.3 Time Zone Functions & Session Environment

To build robust global applications, developers must be able to inspect the database server environment, configure client session time zones dynamically, retrieve current timestamps from various clocks, extract individual datetime components, and perform conversions between time zones.

This section covers the built-in temporal functions, session configuration directives, and conversion expressions tested on the Oracle Database SQL (1Z0-071) certification examination.

---

System Clocks vs. Client Session Clocks

Oracle distinguishes between the Database Server Operating System Clock and the Client Session Time Zone Clock:

+-------------------------------------------------------------------------+
|                SYSTEM CLOCK VS. SESSION CLOCK FUNCTIONS                 |
+-------------------------------------------------------------------------+
|                                                                         |
|   DATABASE SERVER OS CLOCK BASIS (Server Hardware Clock)                |
|   - SYSDATE        : Returns Server OS Date & Time (as DATE)            |
|   - SYSTIMESTAMP   : Returns Server OS Date, Time, Fract Sec, & Offset  |
|                      (as TIMESTAMP WITH TIME ZONE)                      |
|                                                                         |
|   CLIENT SESSION CLOCK BASIS (Adjusted to SESSIONTIMEZONE)              |
|   - CURRENT_DATE   : Returns Session Date & Time (as DATE)              |
|   - CURRENT_TIMESTAMP [(p)] : Returns Session Date, Time, Fract Sec,   |
|                               & Session Time Zone (as TSTZ)             |
|   - LOCALTIMESTAMP [(p)]    : Returns Session Date, Time, & Fract Sec   |
|                               (as TIMESTAMP without time zone)          |
|                                                                         |
+-------------------------------------------------------------------------+

Temporal Functions Reference Matrix

-- Comparing clock outputs across a session set to US Pacific Time (-07:00)
-- Server is located in Frankfurt, Germany (+02:00):
SELECT SYSDATE,               -- 2026-08-15 18:30:00 (Server OS)
       SYSTIMESTAMP,          -- 2026-08-15 18:30:00.123456 +02:00 (Server OS)
       CURRENT_DATE,          -- 2026-08-15 09:30:00 (Client Session)
       CURRENT_TIMESTAMP,     -- 2026-08-15 09:30:00.123456 -07:00 (Client Session TSTZ)
       LOCALTIMESTAMP         -- 2026-08-15 09:30:00.123456 (Client Session TS)
FROM dual;

---

Environment Metadata & Session Configuration

Oracle provides two built-in environment functions to inspect current time zone settings:

1. DBTIMEZONE: Returns the database time zone displacement offset (e.g., '+00:00') or region name set when the database was created.
2. SESSIONTIMEZONE: Returns the time zone offset or region name of the currently connected client session.

-- Inspect database and session time zone environments
SELECT DBTIMEZONE, SESSIONTIMEZONE FROM dual;

Altering Session Time Zone (ALTER SESSION)

A client can dynamically alter its session time zone using ALTER SESSION SET TIME_ZONE in four ways:

-- 1. Using a Named Time Zone Region (Olson TZ Database):
ALTER SESSION SET TIME_ZONE = 'America/New_York';

-- 2. Using a Fixed Numerical Displacement Offset:
ALTER SESSION SET TIME_ZONE = '+05:30';

-- 3. Setting to the Database Server Time Zone:
ALTER SESSION SET TIME_ZONE = DBTIMEZONE;

-- 4. Setting to the Client Operating System Local Time Zone:
ALTER SESSION SET TIME_ZONE = LOCAL;

Exam Tip: Altering SESSIONTIMEZONE immediately changes the values returned by CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP, as well as how TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) columns are displayed. It has no effect on SYSDATE or SYSTIMESTAMP.

---

The EXTRACT Function & Datatype Compatibility

The EXTRACT function retrieves a specific numeric component from a datetime or interval expression.

Syntax

$\text{EXTRACT}(\text{field FROM } \text{datetime\_or\_interval\_expression})$

Extractable Fields

• YEAR, MONTH, DAY
• HOUR, MINUTE, SECOND
• TIMEZONE_HOUR, TIMEZONE_MINUTE
• TIMEZONE_REGION, TIMEZONE_ABBR

Datatype Compatibility & Exam Traps

Critical Exam Trap: EXTRACT(HOUR FROM SYSDATE) Raises ORA-30076! Although Oracle's internal DATE datatype physically stores hours, minutes, and seconds, the ANSI SQL standard definition of DATE contains only YEAR, MONTH, and DAY. Therefore, attempting EXTRACT(HOUR FROM SYSDATE) fails with ORA-30076: invalid extract field for extract source. To extract the hour, you must either extract from a TIMESTAMP (EXTRACT(HOUR FROM SYSTIMESTAMP)) or cast SYSDATE (EXTRACT(HOUR FROM CAST(SYSDATE AS TIMESTAMP))).

-- Valid and Invalid EXTRACT queries:
SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;       -- Returns 2026 (VALID)
SELECT EXTRACT(MONTH FROM SYSDATE) FROM dual;      -- Returns 8 (VALID)
SELECT EXTRACT(HOUR FROM SYSDATE) FROM dual;       -- FAILS with ORA-30076!
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) FROM dual;  -- Returns current server hour (VALID)
SELECT EXTRACT(TIMEZONE_REGION FROM SYSTIMESTAMP) FROM dual; -- Returns server TZ region/offset (VALID)

---

Temporal Conversion Functions

Oracle provides specialized conversion functions for constructing and transforming time-zone-aware datatypes and intervals:

+-------------------------------------------------------------------------+
|                     TEMPORAL CONVERSION FUNCTIONS                       |
+-------------------------------------------------------------------------+
| Function              | Purpose & Conversion Path                       |
| :-------------------- | :---------------------------------------------- |
| FROM_TZ(ts, tz)       | Converts TIMESTAMP to TIMESTAMP WITH TIME ZONE  |
| TO_TIMESTAMP_TZ(s, f) | Parses string to TIMESTAMP WITH TIME ZONE       |
| TO_YMINTERVAL(s)      | Parses string 'Y-M' to INTERVAL YEAR TO MONTH   |
| TO_DSINTERVAL(s)      | Parses string 'D HH:MI:SS' to INTERVAL DAY TO SEC|
+-------------------------------------------------------------------------+

1. FROM_TZ

FROM_TZ combines a standalone TIMESTAMP value and a time zone specification into a TIMESTAMP WITH TIME ZONE:

-- Combine TIMESTAMP literal with named region
SELECT FROM_TZ(TIMESTAMP '2026-08-15 14:30:00', 'America/Chicago') AS tstz_val
FROM dual;
-- Returns: 15-AUG-26 02.30.00.000000000 PM AMERICA/CHICAGO

Exam Trap: The first argument of FROM_TZ must be a TIMESTAMP datatype. Passing a DATE or VARCHAR2 string directly without casting raises ORA-00932: inconsistent datatypes.

2. TO_TIMESTAMP_TZ

TO_TIMESTAMP_TZ converts character strings into TIMESTAMP WITH TIME ZONE using explicit format masks:

• TZH / TZM: Time Zone Hour and Minute displacement (e.g., +05:30).
• TZR: Time Zone Region name (e.g., 'Europe/Berlin').
• TZD: Daylight saving time information (e.g., 'EDT', 'PST').

SELECT TO_TIMESTAMP_TZ('2026-08-15 16:45:00.500 -04:00', 
                       'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') AS tstz_val
FROM dual;

3. TO_YMINTERVAL and TO_DSINTERVAL

These functions parse formatted character strings into interval datatypes:

-- Convert '1-6' to INTERVAL YEAR TO MONTH (1 year, 6 months):
SELECT TO_YMINTERVAL('01-06') FROM dual;

-- Convert '10 08:30:00' to INTERVAL DAY TO SECOND (10 days, 8 hours, 30 mins):
SELECT TO_DSINTERVAL('10 08:30:00') FROM dual;

---

The AT TIME ZONE Expression

The AT TIME ZONE clause allows on-the-fly conversion of a timestamp with time zone value to a different time zone for display or comparative evaluation:

$\text{timestamp\_with\_timezone\_expression } \text{AT TIME ZONE } \text{'timezone\_spec'}$

-- Convert SYSTIMESTAMP (Server time) to Tokyo time for display:
SELECT SYSTIMESTAMP AT TIME ZONE 'Asia/Tokyo' AS tokyo_time,
       SYSTIMESTAMP AT TIME ZONE 'America/New_York' AS ny_time,
       SYSTIMESTAMP AT TIME ZONE DBTIMEZONE AS db_time
FROM dual;

Using AT LOCAL

The shorthand AT LOCAL converts the timestamp expression into the client's current session time zone (SESSIONTIMEZONE):

SELECT trade_id, executed_at AT LOCAL AS local_execution_time
FROM trade_executions;