Pages

Friday, February 10, 2012

DATE and DST

Recently on the ODTUG e-mail list ODTUG-SQLPLUS-L a user highcharge asked how to take daylight savings time into consideration when calculating number of hours between two DATE values. If you merely subtract the dates, twice a year you will be one hour wrong.

Ideally you might switch to datatype TIMESTAMP WITH TIMEZONE, but sometimes that is not an option (or sometimes perhaps "overkill" for the situation :-) Fortunately there is another way.

The definition of Daylight Savings Time (DST) is not the same all over the world. Most of the USA uses DST while some of it (like Hawaii) doesn't. Europe switches to DST a couple weeks later than USA. So you cannot just code some logic based on a given set of dates.

Luckily Oracle has done the work for us and know the definitions of DST throughout most (if not all) of the world.If you select from view v$timezone_names you will see all the timezones known to your installation and a bunch of aliases too. For a list of the most "meaningful" timezone names in your Oracle, try this select:

Oracle has a built-in function NEW_TIME that takes a DATE parameter and two timezones. It will interpret the DATE value as being in the first timezone and return that value converted to the second timezone. Unfortunately NEW_TIME has been created for Americans only, it will not work for any other timezones :-(

function FROM_TZ converts the timestamp from p_from_tz to the database timezone (doesn't matter what that is)

the expression AT TIME ZONE converts from database timezone to p_to_tz

and the result is cast back to DATE datatype

Using that function we can calculate correct number of hours between two dates - we just have to know "somehow" what timezone the data originates from. Then we can convert the two dates to for example UTC and then simply subtract the dates.

An example:

We have workers checking in and checking out from work. We have departments several places in the world. Lets for an example have 4 workers in Copenhagen, Alaska, US Eastern time and Hawaii. They are all night shift workers starting work at 22:00 (10 PM) and stopping at 06:00 (6 AM.)

In 2011 most of USA switched to DST on March 13th, while most of Europe did it two weeks later on March 27th. So if we try the start and stop times for those 4 workers for those two nights:

Local start/stop time is identical for all 4 workers. Then we use their timezone to convert their local time to UTC time. We use the UTC time for calculating the hours they worked.

We see that the Copenhagen worker only worked 7 hours the night of March 26/27. The Alaska and Eastern worker did 7 hours the night of March 12/13. But the Hawaiian worker did 8 hours both nights - he does not use DST.

So now you can calculate differences between dates accurately even those two nights a year where DST starts and stops. All you need is knowing the time zone, which could come from several places depending on your situation:

You could be a single-country shop and you know that all your DATE values are in one single timezone - then create a constant in your "global constants" package (if you have one :-) with the value of your timezone.

You might have departments throughout the world like this example - then you could have a column TZ in your DEPT table.

You might have addresses on employees or customers worldwide - then maybe you could have a table with COUNTRY, ZIPCODE, TZ to locate the timezone of a person by his address. (Such a table can be bought from various data vendors.)

Perhaps you go the advanced way and use Oracle Spatial or Google for geolocating an adress to get latitude/longitude, which you could transform to timezone at AskGeo or other similar service.