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:


SQL> select distinct tz.tzname
  2  from v$timezone_names tz
  3  where tz.tzname like '%/%'
  4  and tz.tzname not like 'Etc/GMT%'
  5  order by tz.tzname;

TZNAME
----------------------------------------------------------------
Africa/Algiers
Africa/Cairo
Africa/Casablanca
Africa/Ceuta
Africa/Djibouti
...
Europe/Bucharest
Europe/Budapest
Europe/Copenhagen
Europe/Dublin
...
US/Michigan
US/Mountain
US/Pacific
US/Pacific-New
US/Samoa

319 rows selected.


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 :-(

So we make another version of NEW_TIME:


SQL> create or replace function date_tz_convert(
  2     p_date      date,
  3     p_from_tz   varchar2,
  4     p_to_tz     varchar2
  5  )
  6     return date
  7  is
  8  begin
  9     return cast(from_tz(cast(p_date as timestamp),p_from_tz) at time zone p_to_tz as date);
 10  end date_tz_convert;
 11  /

Function created.


This function works like this:

  • p_date is cast to a TIMESTAMP datatype
  • 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:


SQL> with work as (
  2     select
  3        to_date('2011-03-12 22:00:00','YYYY-MM-DD HH24:MI:SS') start_date,
  4        to_date('2011-03-13 06:00:00','YYYY-MM-DD HH24:MI:SS') stop_date
  5     from dual
  6     union all
  7     select
  8        to_date('2011-03-26 22:00:00','YYYY-MM-DD HH24:MI:SS') start_date,
  9        to_date('2011-03-27 06:00:00','YYYY-MM-DD HH24:MI:SS') stop_date
 10     from dual
 11  ), places as (
 12     select 'Europe/Copenhagen' tz from dual union all
 13     select 'US/Alaska' tz from dual union all
 14     select 'US/Eastern' tz from dual union all
 15     select 'US/Hawaii' tz from dual
 16  )
 17  --
 18  -- end of test data
 19  --
 20  select
 21     tz,
 22     to_char(start_local,'Mon DD HH24:MI') start_local,
 23     to_char(stop_local ,'Mon DD HH24:MI') stop_local,
 24     to_char(start_utc  ,'Mon DD HH24:MI') start_utc,
 25     to_char(stop_utc   ,'Mon DD HH24:MI') stop_utc,
 26     (stop_utc - start_utc) * 24 workhours
 27  from (
 28     select
 29        places.tz,
 30        work.start_date start_local,
 31        work.stop_date  stop_local,
 32        date_tz_convert(work.start_date, places.tz, 'UTC') start_utc,
 33        date_tz_convert(work.stop_date , places.tz, 'UTC') stop_utc
 34     from work
 35     cross join places
 36  )
 37  order by
 38     tz,
 39     start_local;

TZ                START_LOCAL  STOP_LOCAL   START_UTC    STOP_UTC      WORKHOURS
----------------- ------------ ------------ ------------ ------------ ----------
Europe/Copenhagen Mar 12 22:00 Mar 13 06:00 Mar 12 21:00 Mar 13 05:00          8
Europe/Copenhagen Mar 26 22:00 Mar 27 06:00 Mar 26 21:00 Mar 27 04:00          7
US/Alaska         Mar 12 22:00 Mar 13 06:00 Mar 13 07:00 Mar 13 14:00          7
US/Alaska         Mar 26 22:00 Mar 27 06:00 Mar 27 06:00 Mar 27 14:00          8
US/Eastern        Mar 12 22:00 Mar 13 06:00 Mar 13 03:00 Mar 13 10:00          7
US/Eastern        Mar 26 22:00 Mar 27 06:00 Mar 27 02:00 Mar 27 10:00          8
US/Hawaii         Mar 12 22:00 Mar 13 06:00 Mar 13 08:00 Mar 13 16:00          8
US/Hawaii         Mar 26 22:00 Mar 27 06:00 Mar 27 08:00 Mar 27 16:00          8

8 rows selected.


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.


We can use this for a "timezone-aware" date diff function:


SQL> create or replace function date_tz_diff(
  2     p_start_date   date,
  3     p_stop_date    date,
  4     p_tz           varchar2
  5  )
  6     return number
  7  is
  8  begin
  9     return date_tz_convert(p_stop_date , p_tz, 'UTC')
 10          - date_tz_convert(p_start_date, p_tz, 'UTC');
 11  end date_tz_diff;
 12  /

Function created.

And our example could then become:


SQL> with work as (
  2     select
  3        to_date('2011-03-12 22:00:00','YYYY-MM-DD HH24:MI:SS') start_date,
  4        to_date('2011-03-13 06:00:00','YYYY-MM-DD HH24:MI:SS') stop_date
  5     from dual
  6     union all
  7     select
  8        to_date('2011-03-26 22:00:00','YYYY-MM-DD HH24:MI:SS') start_date,
  9        to_date('2011-03-27 06:00:00','YYYY-MM-DD HH24:MI:SS') stop_date
 10     from dual
 11  ), places as (
 12     select 'Europe/Copenhagen' tz from dual union all
 13     select 'US/Alaska' tz from dual union all
 14     select 'US/Eastern' tz from dual union all
 15     select 'US/Hawaii' tz from dual
 16  )
 17  --
 18  -- end of test data
 19  --
 20  select
 21     places.tz,
 22     to_char(work.start_date,'Mon DD HH24:MI') start_local,
 23     to_char(work.stop_date ,'Mon DD HH24:MI') stop_local,
 24     date_tz_diff(work.start_date, work.stop_date, places.tz) * 24 workhours
 25  from work
 26  cross join places
 27  order by
 28     places.tz,
 29     work.start_date;

TZ                START_LOCAL  STOP_LOCAL    WORKHOURS
----------------- ------------ ------------ ----------
Europe/Copenhagen Mar 12 22:00 Mar 13 06:00          8
Europe/Copenhagen Mar 26 22:00 Mar 27 06:00          7
US/Alaska         Mar 12 22:00 Mar 13 06:00          7
US/Alaska         Mar 26 22:00 Mar 27 06:00          8
US/Eastern        Mar 12 22:00 Mar 13 06:00          7
US/Eastern        Mar 26 22:00 Mar 27 06:00          8
US/Hawaii         Mar 12 22:00 Mar 13 06:00          8
US/Hawaii         Mar 26 22:00 Mar 27 06:00          8

8 rows selected.


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.

The possibilities are endless ;-)

No comments:

Post a Comment