1

I'm working on optimizing some functions in which I need to convert many timestamps to UTC. My need is to create a table with the columns as follows:

YEAR | TZ_NAME | Start of Daylight Savings | End of Daylight Savings

I can convert the timestamps easily using the from_tz function in oracle, however running this per timestamp isn't an option. Does anyone know how the oracle function works? I can't find any documentation that details where oracle keeps these daylight savings rules.

Any suggestions?

Thank you!

ddevlin1
  • 45
  • 7

1 Answers1

2

The time zone related information is stored in a file, as per: https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006667

The Oracle time zone files contain the valid time zone names. The following information is also included for each time zone:

•Offset from Coordinated Universal Time (UTC)

•Transition times for Daylight Saving Time

•Abbreviations for standard time and Daylight Saving Time

Boneist
  • 22,910
  • 1
  • 25
  • 40
  • I suppose this is as good as it will get for me.. I was hoping there was a secret view that Oracle might provide on top of this file to give more visibility. Thanks for providing that link. – ddevlin1 Mar 04 '16 at 16:32
  • 1
    Sadly, not that I could find (I queried dba_views to see if there was anything with TIME%ZONE in the name, but only v$timezone_file and v$timezone_names were there), neither of which has the info you're after. – Boneist Mar 04 '16 at 16:36
  • 1
    Just a note, daylight saving times and time zones are not as constant as most people think about. For example, have a look at [Singapore Standard Time](https://en.wikipedia.org/wiki/Singapore_Standard_Time) or [Time in Malaysia](https://en.wikipedia.org/wiki/Time_in_Malaysia), within 80 years they changed their time zone 6 times! – Wernfried Domscheit Mar 04 '16 at 17:49
  • It's definitely a difficult rule set to maintain, which is why everyone should just store times in UTC! I found this collection of best practices to be interesting: [DST and TZ Best Practices](http://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices?rq=1) – ddevlin1 Mar 04 '16 at 18:35