Oracle TZ_OFFSET Function: A Simple Guide

The TZ_OFFSET function in Oracle SQL is a simple utility that returns the current time zone offset from UTC for a given time zone name.

For example, it can tell you that 'US/Eastern' is currently at '-04:00' (during Daylight Saving) or '-05:00' (during Standard Time). It's "Daylight Saving Time aware" and bases its result on the current date.

What is the TZ_OFFSET Function in Oracle?

The TZ_OFFSET(time_zone_name) function takes a time zone region name and returns its offset from UTC (Coordinated Universal Time) as a string.

This is useful when you need to know the UTC offset for a region but don't want to query a full timestamp. The function can also accept the keywords SESSIONTIMEZONE or DBTIMEZONE as its argument.

TZ_OFFSET Function Syntax

The syntax for TZ_OFFSET is as follows:

TZ_OFFSET(time_zone_name | SESSIONTIMEZONE | DBTIMEZONE)

Let's break that down:

• time_zone_name: A string with a valid time zone region name (e.g., 'US/Pacific', 'Europe/London').
• SESSIONTIMEZONE: A keyword that returns the offset of your current SQL session.
• DBTIMEZONE: A keyword that returns the offset of the database's time zone.

Oracle TZ_OFFSET Function Examples

Here are two practical examples of how to use TZ_OFFSET.

Example 1: Finding the Offset for a Region Name with TZ_OFFSET

This example finds the current UTC offset for the 'US/Pacific' and 'Europe/Paris' time zones. The result will change depending on the time of year (due to Daylight Saving Time).

Query:

SELECT 
  TZ_OFFSET('US/Pacific') AS "Pacific_Offset",
  TZ_OFFSET('Europe/Paris') AS "Paris_Offset"
FROM DUAL;

Result: (The exact result depends on the date the query is run)

Pacific_Offset Paris_Offset
-------------- ------------
-07:00         +02:00

Example 2: Checking Session and Database Time Zone Offsets with TZ_OFFSET

This example uses the SESSIONTIMEZONE and DBTIMEZONE keywords to get their current offsets.

Query:

SELECT 
  TZ_OFFSET(SESSIONTIMEZONE) AS "My_Session_Offset",
  TZ_OFFSET(DBTIMEZONE) AS "Database_Offset"
FROM DUAL;

Result: (This result will vary based on your session and database settings)

My_Session_Offset Database_Offset
----------------- ---------------
+05:30            +00:00