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 :-(
So we make another version of NEW_TIME:
This function works like this:
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.
We can use this for a "timezone-aware" date diff function:
And our example could then become:
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:
The possibilities are endless ;-)
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 ;-)
Comments
Post a Comment