Is oracle DB able to return timezone region rather than just the offset?

1
votes

Is oracle DB able to return timezone region e.g. Europe/London rather than just the offset? I will like to know the region name where the server resides.

SELECT SYSTIMESTAMP param FROM DUAL; returns date, time and offset:

2020-08-17 18:11:03.219138 -05:00

SELECT DBTIMEZONE FROM dual; returns offset:

-05:00
1
oracletimezonetimezone-offset

1 Answers

3
votes

Oracle gets only timezone offset from OS, so it can't say what Region is set in OS, because there a lot of timezone regions with the same offset (-05:00). But you can execute OS commands from within Oracle: tzutil /g on windows or timedatectl on Linux (or just read /etc/timezone) to get extended info.

Update: for example, you can use my package xt_shell to run OS commands with timeout and extended STDERR.

https://github.com/xtender/xt_shell

select * from table(xt_shell.shell_exec('/usr/bin/timedatectl',1000));

Result:

SQL> select * from table(xt_shell.shell_exec('/usr/bin/timedatectl',1000));

COLUMN_VALUE
---------------------------------------------------
      Local time: Tue 2020-08-18 11:25:06 UTC
  Universal time: Tue 2020-08-18 11:25:06 UTC
        RTC time: Tue 2020-08-18 11:25:06
       Time zone: Europe/Berlin (UTC, +0000)
     NTP enabled: yes
NTP synchronized: yes
 RTC in local TZ: no
      DST active: n/a