Oracle SESSIONTIMEZONE Function: A Simple Guide

The SESSIONTIMEZONE function in Oracle SQL is a simple function that returns the time zone of your current SQL session or connection.

This is different from DBTIMEZONE, which shows the time zone of the entire database server. SESSIONTIMEZONE tells you the time zone that is currently active for you.

What is the SESSIONTIMEZONE Function in Oracle?

The SESSIONTIMEZONE function returns a single string that shows your session's time zone. The value returned will be in one of two formats:

1. An offset: A string showing the offset from UTC (e.g., +05:30 or -08:00).
2. A region name: A string showing the time zone region name (e.g., US/Eastern or Europe/London).

This value is controlled by the ALTER SESSION SET TIME_ZONE = ... command, or by defaults set by your client (like SQL Developer) or environment.

SESSIONTIMEZONE Function Syntax

The syntax for SESSIONTIMEZONE is one of the simplest in Oracle, as it requires no arguments:

SESSIONTIMEZONE

Oracle SESSIONTIMEZONE Function Examples

Here are two practical examples of how to use SESSIONTIMEZONE.

Example 1: Checking the Current Session Time Zone with SESSIONTIMEZONE

This example simply shows you what your current session's time zone is set to.

Query:

SELECT 
  SESSIONTIMEZONE 
FROM DUAL;

Result: (Your result will vary based on your connection's settings. This is a common default.)

SESSIONTIMEZONE
---------------
-08:00

Example 2: Seeing SESSIONTIMEZONE Change After an ALTER SESSION

This is the most practical way to understand the function. Let's see how the output of SESSIONTIMEZONE changes after we actively alter our session.

Query:

-- 1. First, set our session time zone to a region name
ALTER SESSION SET TIME_ZONE = 'US/Eastern';

-- 2. Now, check the session time zone
SELECT 
  SESSIONTIMEZONE 
FROM DUAL;

-- 3. Next, set our session time zone to a numerical offset
ALTER SESSION SET TIME_ZONE = '+02:00';

-- 4. Check the session time zone again
SELECT 
  SESSIONTIMEZONE 
FROM DUAL;

Result:

(Result of the first SELECT statement)

SESSIONTIMEZONE
---------------
US/Eastern

(Result of the second SELECT statement)

SESSIONTIMEZONE
---------------
+02:00