Sometimes we need to specify a time zone when using datetime functions in Oracle.

But there are a lot of time zones out there. How do we remember them all? And how do we know our system supports a given time zone?

Fortunately, we can query the V$TIMEZONE_NAMES view to return a list of valid time zones.

Get All Time Zone Info

The following code returns all rows and columns from the view:

SELECT * FROM V$TIMEZONE_NAMES;

There are a lot of rows in this view, but here’s what the first 10 rows looks like:

               TZNAME    TZABBREV    CON_ID 
_____________________ ___________ _________ 
Africa/Abidjan        LMT                 0 
Africa/Abidjan        GMT                 0 
Africa/Accra          LMT                 0 
Africa/Accra          GMT                 0 
Africa/Accra          +0020               0 
Africa/Addis_Ababa    LMT                 0 
Africa/Addis_Ababa    EAT                 0 
Africa/Addis_Ababa    +0230               0 
Africa/Addis_Ababa    +0245               0 
Africa/Algiers        LMT                 0 
...

We can see that each time zone region name has more than one corresponding abbreviation. This caters for things like daylight savings, etc.

Here’s a table that outlines what some of the abbreviations mean:

Time Zone Abbreviation	Meaning
LMT	Local Mean Time
PMT	Paris Mean Time
WET	Western European Time
WEST	Western European Summer Time
CET	Central Europe Time
CEST	Central Europe Summer Time
EET	Eastern Europe Time
EEST	Eastern Europe Summer Time

Get Just the Region Names

We can use the DISTINCT or UNIQUE clause to return just the time zone region names without duplicates.

SELECT DISTINCT TZNAME 
FROM V$TIMEZONE_NAMES
ORDER BY TZNAME ASC;

Here’s what the first 10 rows looks like now:

               TZNAME 
_____________________ 
Africa/Abidjan        
Africa/Accra          
Africa/Addis_Ababa    
Africa/Algiers        
Africa/Asmara         
Africa/Asmera         
Africa/Bamako         
Africa/Bangui         
Africa/Banjul         
Africa/Bissau         
...

Get All Abbreviations for a Given Time Zone Name

Here’s an example of a query that returns all the abbreviations for a given time zone name:

SELECT 
    TZNAME,
    TZABBREV
FROM V$TIMEZONE_NAMES
WHERE TZNAME LIKE '%Sydney%'
ORDER BY TZNAME ASC;

Result:

             TZNAME    TZABBREV 
___________________ ___________ 
Australia/Sydney    LMT         
Australia/Sydney    AEDT        
Australia/Sydney    AEST        

About Time Zone Files

In Oracle Database, time zone names are contained in time zone files.

You can query the V$TIMEZONE_FILE view to see which time zone file is currently being used by the database.

Example:

SELECT * FROM V$TIMEZONE_FILE;

Result:

          FILENAME    VERSION    CON_ID 
__________________ __________ _________ 
timezlrg_32.dat            32         0 

My system is using version 32, which is the default time zone file for Oracle 19c (which is what I’m using). This file is located at the following location: $ORACLE_HOME/oracore/zoneinfo/timezlrg_32.dat

See Oracle’s documentation for Choosing a Time Zone File for more information about time zone files.