16.1 Datetime Data Types & Time Zone Offsets

16.1 Datetime Data Types & Time Zone Offsets

Modern enterprise applications operate across global boundaries, coordinating transactions, financial settlements, and audit logs across multiple continents and time zones. To manage temporal data reliably, Oracle Database provides a sophisticated hierarchy of date and timestamp datatypes designed to handle varying levels of precision and time zone sensitivity.

For the Oracle Database SQL (1Z0-071) examination, candidates must master the structural, storage, and behavioral distinctions among Oracle's four foundational datetime datatypes: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE (TSTZ), and TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ).

---

Evolution of Oracle Temporal Datatypes

Oracle's datetime datatypes evolved to meet the demands of increasingly precise and geographically dispersed database environments:

+-------------------------------------------------------------------------+
|                 EVOLUTION OF ORACLE TEMPORAL DATATYPES                  |
+-------------------------------------------------------------------------+
|                                                                         |
|   1. DATE                                                               |
|      - Century, Year, Month, Day, Hour, Minute, Second                  |
|      - Second-level resolution; No fractional seconds; No time zone     |
|                                                                         |
|   2. TIMESTAMP [(fractional_seconds_precision)]                         |
|      - Extends DATE with fractional seconds (0 to 9 digits, default 6)  |
|      - Microsecond precision; No time zone awareness                    |
|                                                                         |
|   3. TIMESTAMP [(p)] WITH TIME ZONE (TSTZ)                              |
|      - Date + Time + Fractional Seconds + Explicit Time Zone Offset/Name|
|      - Preserves original input time zone context permanently           |
|                                                                         |
|   4. TIMESTAMP [(p)] WITH LOCAL TIME ZONE (TSLTZ)                       |
|      - Normalized to DBTIMEZONE on disk; Displayed in SESSIONTIMEZONE   |
|      - Space-efficient local representation; No stored time zone offset |
|                                                                         |
+-------------------------------------------------------------------------+

---

1. The DATE Datatype

The traditional DATE datatype is Oracle's original temporal datatype. It stores seven temporal components in a fixed 7-byte binary format:

1. Century (CC)
2. Year (YY)
3. Month (MM)
4. Day (DD)
5. Hour (HH24)
6. Minute (MI)
7. Second (SS)

Characteristics and Limitations

• Resolution: Maximum resolution is one second. It cannot record milliseconds, microseconds, or nanoseconds.
• Time Zone Ignorance: DATE stores no time zone offset, displacement, or region name. It represents a "naive" date and time.
• Default Display: Governed by the session's NLS_DATE_FORMAT parameter (commonly DD-MON-RR or YYYY-MM-DD). Even if time is omitted in display formatting, DATE always contains the time component (defaulting to midnight 00:00:00 if unspecified upon insertion).

-- Create a table with standard DATE column
CREATE TABLE app_events (
    event_id    NUMBER(8) PRIMARY KEY,
    event_name  VARCHAR2(50) NOT NULL,
    event_date  DATE DEFAULT SYSDATE
);

---

2. The TIMESTAMP Datatype

The TIMESTAMP datatype extends DATE by introducing support for fractional seconds, allowing precise sub-second event recording.

Syntax & Precision

$\text{TIMESTAMP } [(\text{fractional\_seconds\_precision})]$

• fractional_seconds_precision: An integer from 0 to 9 specifying the number of digits in the fractional part of the seconds component.
• Default Precision: If omitted, the default precision is 6 decimal places (microseconds, e.g., .123456).
• Storage Size: Requires 7 bytes (if precision is 0) to 11 bytes (if precision is 1-9).

-- Column definitions demonstrating explicit vs default precision
CREATE TABLE sensor_readings (
    reading_id       NUMBER(10) PRIMARY KEY,
    recorded_default TIMESTAMP,       -- Defaults to TIMESTAMP(6)
    recorded_milli   TIMESTAMP(3),    -- Millisecond precision (0.001s)
    recorded_nano    TIMESTAMP(9)     -- Nanosecond precision (0.000000001s)
);

Exam Tip: Just like DATE, standard TIMESTAMP has no time zone awareness. It records fractional seconds, but does not know which geographic or UTC offset the timestamp belongs to.

---

3. TIMESTAMP WITH TIME ZONE (TSTZ)

TIMESTAMP WITH TIME ZONE stores date, time, fractional seconds, and an explicit time zone displacement offset or named time zone region.

Characteristics

• Explicit Retention: When data is inserted with an offset (e.g., '-05:00') or region name (e.g., 'America/New_York'), Oracle preserves that exact time zone representation in the stored row.
• Display Behavior: When queried, the data is returned exactly as stored—preserving the original time zone displacement or region name regardless of the querying client's session time zone.
• Internal Storage: Requires 13 bytes of storage per row (to hold datetime fields, fractional seconds, and time zone offset/region ID bytes).
• Primary Use Case: Legal contracts, flight scheduling, international wire transfers, and cross-border audit compliance where the exact local time and geographic jurisdiction of the original transaction must be preserved.

-- Table storing international trade execution with original local context
CREATE TABLE trade_executions (
    trade_id       NUMBER(12) PRIMARY KEY,
    symbol         VARCHAR2(10) NOT NULL,
    executed_at    TIMESTAMP(6) WITH TIME ZONE
);

-- Inserting with explicit time zone displacement and named region
INSERT INTO trade_executions VALUES (
    101, 'AAPL', TO_TIMESTAMP_TZ('2026-08-15 09:30:00.000000 -04:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')
);

INSERT INTO trade_executions VALUES (
    102, 'HSBC', TO_TIMESTAMP_TZ('2026-08-15 14:30:00.000000 Europe/London', 'YYYY-MM-DD HH24:MI:SS.FF TZR')
);

---

4. TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ)

TIMESTAMP WITH LOCAL TIME ZONE provides time-zone-aware normalization with automatic client session adjustment, without storing an explicit time zone in the table.

Storage & Retrieval Mechanics

1. Upon Insertion/Storage: Oracle automatically converts the input timestamp from the client's current session time zone (SESSIONTIMEZONE) into the database time zone (DBTIMEZONE), storing the normalized UTC/database time on disk in 7 to 11 bytes (the same compact format as standard TIMESTAMP).
2. Upon Query/Retrieval: Oracle automatically converts the stored value from DBTIMEZONE into the querying client's current session time zone (SESSIONTIMEZONE).

+-------------------------------------------------------------------------+
|               TSTZ VS. TSLTZ DATA FLOW COMPARISON                       |
+-------------------------------------------------------------------------+
|                                                                         |
|  Scenario: Database DBTIMEZONE = '+00:00' (UTC)                         |
|  Client 1 (Tokyo, +09:00) inserts '2026-08-15 18:00:00'                 |
|                                                                         |
|  [ TSTZ Column ]                                                        |
|  - Stored on Disk: '2026-08-15 18:00:00 +09:00' (13 bytes)              |
|  - Client 1 (+09:00) Queries: '2026-08-15 18:00:00 +09:00'             |
|  - Client 2 (NY, -04:00) Queries: '2026-08-15 18:00:00 +09:00'         |
|  (Value never changes based on who is querying)                         |
|                                                                         |
|  [ TSLTZ Column ]                                                       |
|  - Normalized on Disk: '2026-08-15 09:00:00' (UTC, 7-11 bytes)          |
|  - Client 1 (+09:00) Queries: '2026-08-15 18:00:00' (converted to +9)  |
|  - Client 2 (NY, -04:00) Queries: '2026-08-15 05:00:00' (converted to -4)|
|  (Value dynamically shifts to match querying user's local clock)        |
|                                                                         |
+-------------------------------------------------------------------------+

Primary Use Case

Internal enterprise workflows, project management milestones, and helpdesk ticketing where global users should see all dates and times automatically rendered in their own local time without manual calculation.

---

Comprehensive 4-Datatype Comparison Matrix

---

Named Regions vs. Numerical Offsets & Daylight Saving Time (DST)

When specifying time zones in Oracle SQL, you can use either a numerical UTC displacement offset or a named time zone region:

-- Numerical Offset (Fixed)
'+02:00'
'-05:00'

-- Named Region (Olson Time Zone Database)
'America/New_York'
'Europe/Paris'
'Asia/Tokyo'

Critical Difference: Daylight Saving Time (DST)

• Fixed Numerical Offsets: A fixed displacement like '-05:00' never adjusts for Daylight Saving Time. Regardless of whether it is January or July, the offset remains exactly $-5$ hours from UTC.
• Named Time Zone Regions: A region name like 'America/New_York' references Oracle's built-in time zone translation rules (derived from the IANA/Olson database). Oracle automatically calculates whether Daylight Saving Time is in effect for the specific date provided:
  • During Standard Time (EST): 'America/New_York' operates at UTC $-5$.
  • During Daylight Saving Time (EDT): 'America/New_York' operates at UTC $-4$.

-- In Winter (Standard Time - EST = UTC-5):
SELECT TO_TIMESTAMP_TZ('2026-01-15 12:00:00 America/New_York', 'YYYY-MM-DD HH24:MI:SS TZR') FROM dual;
-- Result time zone offset is -05:00

-- In Summer (Daylight Saving Time - EDT = UTC-4):
SELECT TO_TIMESTAMP_TZ('2026-07-15 12:00:00 America/New_York', 'YYYY-MM-DD HH24:MI:SS TZR') FROM dual;
-- Result time zone offset is -04:00

Daylight Saving Time Transition Traps on 1Z0-071

1. Spring Forward (Nonexistent Local Time): When clocks jump forward 1 hour (e.g., from 02:00:00 to 03:00:00), local times between 02:00:00 and 02:59:59 do not exist. Attempting to parse such a timestamp with a region name may result in ORA-01878: specified field not found in datetime or interval unless disambiguated.
2. Fall Back (Ambiguous Local Time): When clocks fall back 1 hour (e.g., from 02:00:00 back to 01:00:00), times between 01:00:00 and 01:59:59 occur twice. Oracle uses standard time by default unless specified with the TZD (Time Zone Daylight) format element.

---

1Z0-071 Exam Traps & Rules for Datetime Types

Exam Trap 1: DBTIMEZONE Modification Restriction with TSLTZ You cannot alter the database time zone (ALTER DATABASE SET TIME_ZONE = ...) if any table in the entire database contains a column defined as TIMESTAMP WITH LOCAL TIME ZONE. Attempting to do so raises ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns.

Exam Trap 2: Default Precision When creating a TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE column without specifying a precision, Oracle always defaults to 6 fractional second digits (microseconds), not 0, 3, or 9.

Exam Trap 3: TSLTZ Columns do NOT Output a Time Zone Indicator When querying a TSLTZ column, the displayed string does not include a time zone offset (such as +02:00) because the value has already been converted into the client's current session time zone. The format is governed by NLS_TIMESTAMP_FORMAT, not NLS_TIMESTAMP_TZ_FORMAT.