0

I have an application that reads PC time zone information using GetTimeZoneInformation. Then when I establish connection to Oracle I need to 'alter session set time_zone = <time_zone>'. If I use just the offset, oracle will disregard daylight saving settings, so I need to specify time zone name. However oracle has over 2000 records in v$timezone_names table, none of them matching the time zones Windows returns via tzutil or GetTimeZoneInformation. Is there any way to convert the time zone windows returns to corresponding time zone in oracle, considering I need to keep dayluight saving settings.

user1617735
  • 451
  • 5
  • 16
  • Note sure about Oracle, but Microsoft now ships ICU that may help you https://learn.microsoft.com/en-us/windows/win32/intl/international-components-for-unicode--icu- if you're not on Windows 10, you'll have to use ICU by yourself: https://github.com/unicode-org/icu – Simon Mourier Mar 02 '21 at 14:19
  • My server runs mostly on 2012 and 2016 servers, not sure if it's there as well. Also I still need to map it to Oracle somehow, hopefully not manually for all 200+ time zones windows has! – user1617735 Mar 02 '21 at 14:24
  • It's not available on 2012 and 2016 but you can still use the .dll if you download ICU release from github. That's a few MB package. – Simon Mourier Mar 02 '21 at 14:40

1 Answers1

0

Actually that's not possible. Oracle uses the IANA Time zone database. Try tzutil /l to get a list all time zones with alternative name.

Or have a look at How to translate between Windows and IANA time zones?

NB, instead of ALTER SESSION SET TIME_ZONE = ... you can also preset the Oracle session time zone by Environment variable ORA_SDTZ or in your Registry at

HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_<Oracle Home>\ORA_SDTZ
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Oracle\KEY_<Oracle Home>_32bit\ORA_SDTZ
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • The registry solution wouldn't work unfortunately. The application that maintains sessions to Oracle is supposed to support multiple time zones per session on the same oracle client, hence alter session. I'll take a look at IANA time zones, thanks! – user1617735 Mar 02 '21 at 14:48