Database timezone versus OS timezone

May 21, 2025

1–2 minutes

Typically when you install a database on a local server, the database time zone an the OS time zone will all be alignment.

However, in the world of global database access this is not mandated to be the case. For example, on the database VM I’m running my laptop here in the Perth, the database time zone is reported as:


SQL> select dbtimezone from dual;

DBTIME
------
+00:00

But the VM itself considers itself in Perth


[oracle@db238 ~]$ timedatectl
               Local time: Wed 2025-05-21 12:31:18 AWST
           Universal time: Wed 2025-05-21 04:31:18 UTC
                 RTC time: Wed 2025-05-21 04:31:19
                Time zone: Australia/Perth (AWST, +0800)

You can of course bring these into alignment with an ALTER DATABASE command, but if the database time zone cannot be altered, there doesn’t seem to be native way from within the database to discover what the OS time zone is set to. SYSTIMESTAMP can give you the time zone offset but not the name, and if you then use the offset to hook back into V$TIMEZONE_NAMES then there is a myriad of potential region names that will match the single offset value.

(If there is a way, please let me know in the comments)

So here is my workaround. We can use the standard trick of an external table pre-processor to get the information we need.

First a little bash script to tidy up our time zone information


[oracle@db238 ~]$ cat /home/oracle/os_timezone.sh 
#!/bin/bash
/usr/bin/timedatectl | \
  /usr/bin/grep "Time zone" | \
  /usr/bin/awk -F: '{print $2}' | \
  /usr/bin/awk '{print $1}'

Then a simple external wrapper around that


SQL> create or replace
  2  directory bin as '/home/oracle';

Directory created.

SQL>
SQL> create or replace
  2  view os_timezone as
  3  select tzone
  4  from   external (
  5           ( tzone varchar2(100) )
  6           type oracle_loader
  7           default directory BIN
  8           access parameters
  9           ( records delimited by newline
 10             preprocessor  bin:'os_timezone.sh'
 11             nobadfile
 12             nologfile
 13             nodiscardfile
 14            )
 15            location ( 'os_timezone.sh' )
 16     reject limit unlimited ) ext;

View created.

And voila! The OS time zone is now available whenever we want.


SQL> select *
  2  from os_timezone;

TZONE
----------------------
Australia/Perth

Addenda: Kudos to Stew Ashton and Iudith Mentzel for pointing out that the TZR/TZD format mask may be sufficient to do the trick and avoid the need for the solution above.

3 responses to “Database timezone versus OS timezone”

iudithd5bf8e4d8d

May 21, 2025 at 3:34 pm

Hi Connor,

Very nice solution 🙂

Just for my understanding:
Isn’t SYSTIMESTAMP supposed to contain the host OS time zone ( if keeping the default TIME_AT_DBTIMEZONE = OFF ) ?

It’s true that most of the time we see the time zone of SYSTIMESTAMP specified as an offset, and not as a region name.

Cheers & Best Regards,
Iudith

Reply
1. Connor McDonald
  
  May 21, 2025 at 3:55 pm
  
  Stew pointed out the same, aka, there’s an omission in the blog that I was after the region name not the offset.
  
  But I’ll add some notes to that effect. I should have been more precise.
  
  Reply
zhekappp

October 31, 2025 at 5:21 am

Having OJVM in the database this works:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED “HostTimezone” ASimport java.util.TimeZone;

public class HostTimezone {public static String getOSTimezone() {return TimeZone.getDefault().getID();}}/

CREATE OR REPLACE FUNCTION get_os_timezoneRETURN VARCHAR2 ASLANGUAGE JAVANAME ‘HostTimezone.getOSTimezone() return java.lang.String’;/

SELECT get_os_timezone FROM DUAL;

Reply