Oracle DBTIMEZONE Function: A Simple Guide

The DBTIMEZONE function in Oracle SQL is a simple function that returns the time zone setting of the database itself.

This is different from SESSIONTIMEZONE, which returns the time zone of your current user session. DBTIMEZONE shows the default time zone that was set when the database was created or last altered.

What is the DBTIMEZONE Function in Oracle?

The DBTIMEZONE function returns a single string value that represents the database's time zone. This value can be either:

• An offset from UTC (e.g., +00:00 or -05:00).
• A time zone region name (e.g., US/Eastern).

This function is useful for understanding the server's default behavior, especially when dealing with TIMESTAMP WITH LOCAL TIME ZONE data types, which are stored relative to the database time zone.

DBTIMEZONE Function Syntax

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

DBTIMEZONE

Oracle DBTIMEZONE Function Examples

Here are two practical examples of how to use DBTIMEZONE.

Example 1: Getting the Database Time Zone with DBTIMEZONE

This example simply selects the DBTIMEZONE from the DUAL table to see what the database's time zone is set to.

Query:

SELECT 
  DBTIMEZONE 
FROM DUAL;

Result: (The result will depend on your specific database's setting. A common default is UTC.)

DBTIME
------
+00:00

Example 2: Comparing DBTIMEZONE vs. SESSIONTIMEZONE

This example shows the difference between the database's time zone (DBTIMEZONE) and your current connection's time zone (SESSIONTIMEZONE).

Query:

-- First, let's change our session's time zone to something different
ALTER SESSION SET TIME_ZONE = '-05:00';

-- Now, let's select both
SELECT 
  DBTIMEZONE,
  SESSIONTIMEZONE
FROM DUAL;

Result: (This shows the database is set to UTC, but our session is set to -05:00.)

DBTIME   SESSIONTIMEZONE
------   ---------------
+00:00   -05:00