ExamFinch.com My Study

Study guide

16.3 Time Zone Functions & Session Environment

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

16.3 Time Zone Functions & Session Environment

Key Takeaways Explain This Topic AI

SYSDATE and SYSTIMESTAMP return the current server operating system date and timestamp, whereas CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP return the client session's local time.

DBTIMEZONE returns the database time zone offset or region, while SESSIONTIMEZONE returns the active client session time zone.

Session time zones can be modified dynamically using ALTER SESSION SET TIME_ZONE with named regions, numerical offsets, LOCAL, or DBTIMEZONE.

The EXTRACT function retrieves individual datetime components, but attempting EXTRACT(HOUR FROM date_col) raises ORA-30076 because DATE does not expose time fields under ANSI SQL extract grammar.

FROM_TZ converts a standalone TIMESTAMP into a TIMESTAMP WITH TIME ZONE, while the AT TIME ZONE clause converts a timestamp with time zone expression into a different time zone for display.

Last updated: August 2026

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

Function Name Return Datatype Clock / Time Zone Basis Includes Fractional Seconds? Reflects ALTER SESSION SET TIME_ZONE ?

  • SYSDATE DATE Database Server OS No (1-sec resolution) No (Always server OS time)
  • SYSTIMESTAMP TIMESTAMP WITH TIME ZONE Database Server OS Yes (Up to 9 digits) No (Always server OS time)
  • CURRENT_DATE DATE Client Session ( SESSIONTIMEZONE ) No (1-sec resolution) Yes (Shifts when session changes)

CURRENT_TIMESTAMP TIMESTAMP WITH TIME ZONE Client Session ( SESSIONTIMEZONE ) Yes (Up to 9 digits) Yes (Shifts when session changes)

LOCALTIMESTAMP TIMESTAMP Client Session ( SESSIONTIMEZONE ) Yes (Up to 9 digits) Yes (Shifts when session changes) -- 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:

DBTIMEZONE : Returns the database time zone displacement offset (e.g., '+00:00' ) or region name set when the database was created.

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 EXTRACT ( field FROM datetime_or_interval_expression ) \text{EXTRACT}(\text{field FROM } \text{datetime\_or\_interval\_expression}) EXTRACT ( field FROM datetime_or_interval_expression )

  • Extractable Fields
  • YEAR , MONTH , DAY
  • HOUR , MINUTE , SECOND
  • TIMEZONE_HOUR , TIMEZONE_MINUTE
  • TIMEZONE_REGION , TIMEZONE_ABBR
  • Datatype Compatibility & Exam Traps
  • Extract Field DATE TIMESTAMP TIMESTAMP WITH TIME ZONE INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND
  • YEAR YES YES YES YES NO ( ORA-30076 )
  • MONTH YES YES YES YES NO ( ORA-30076 )
  • DAY YES YES YES NO ( ORA-30076 ) YES
  • HOUR NO ( ORA-30076 ) YES YES NO ( ORA-30076 ) YES
  • MINUTE NO ( ORA-30076 ) YES YES NO ( ORA-30076 ) YES
  • SECOND NO ( ORA-30076 ) YES YES NO ( ORA-30076 ) YES
  • TIMEZONE_HOUR NO ( ORA-30076 ) NO ( ORA-30076 ) YES NO ( ORA-30076 ) NO ( ORA-30076 )
  • TIMEZONE_MINUTE NO ( ORA-30076 ) NO ( ORA-30076 ) YES NO ( ORA-30076 ) NO ( ORA-30076 )
  • TIMEZONE_REGION NO ( ORA-30076 ) NO ( ORA-30076 ) YES NO ( ORA-30076 ) NO ( ORA-30076 )
  • TIMEZONE_ABBR NO ( ORA-30076 ) NO ( ORA-30076 ) YES NO ( ORA-30076 ) NO ( ORA-30076 )

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: timestamp_with_timezone_expression AT TIME ZONE ’timezone_spec’ \text{timestamp\_with\_timezone\_expression } \text{AT TIME ZONE } \text{'timezone\_spec'} timestamp_with_timezone_expression AT TIME ZONE ’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;