PFunked

Handling DST in Oracle

Many of my colleagues are scrambling to retrofit computer software systems to handle the new (as of 2007) Daylight Saving Time rules. I've been working mostly in PL/SQL lately, and I have a tip to pass along.

As you know, Oracle has two common date/time formats: DATE and TIMESTAMP. As of Oracle 9i, TIMESTAMP values have a built-in time zone designation. DATEs have no built-in time zone data.

The Limitations of DATEs

When converting DATE between different time zones (e.g., the local time zone to GMT or back), a coder will usually use the built-in function NEW_TIME(). NEW_TIME uses TZDs as parameters to designate the source and target time zones. TZDs look like: 'EST', 'PDT', 'GMT'. If your source or target time zone observes DST, you need to determine if that date stamp is during standard or daylight saving time. People usually cobble together code that looks like:

if (year <  2007) then
  if (dt < first_sunday_of_april) then
...

You see that this code is a mess to maintain, especially if Congress decides to change these rules again.

The Flexibility of TIMESTAMPs

Timestamps have a function FROM_TZ that works similarly to NEW_TIME. The advantage is that it can accept TZRs along with TZDs. TZRs look like 'US/Central', 'US/Arizona', etc. There is no separate TZR for standard and Daylight Saving time. If my TIMESTAMP data has the correct time zone and I need to convert to prevailing Central time:

new_timestamp := from_tz(my_timestamp, 'US/Central');

I don't need to know whether the source or target is in Standard or Daylight time; Oracle's internal conversions are DST sensitive when using TZRs.

The Wrapper for DATEs

What if our data is all in DATE format, but we don't want to write custom, messy code to figure out whether the data is in standard or Daylight Saving time? We can easily conert to TIMESTAMP, let Oracle do the correct DST-sensitive conversion, and return the result as a DATE.

Examples:

-- convert the source GMT-value DATE to local Pacific time
dest_date := from_tz(to_timestamp(source_date), 'GMT') 
             at time zone 'US/Pacific';

-- convert the source local Pacific time DATE to Arizona time
dest_date := from_tz(to_timestamp(source_date), 'US/Pacific') 
             at time zone 'US/Arizona';

Because we do the time zone switch with Timestamps using DST-sensitive TZRs, we allow Oracle's built-in DST handling to do the heavy lifting for us.

I suggest making a function like this:

create or replace function convert_tz(
  in_date in DATE,
  src_tzr in VARCHAR2,
  dest_tzr in VARCHAR2
) return date
is
begin
  return from_tz(to_timestamp(in_date), src_tzr) 
    at time zone dest_tzr;
end;

Notes

• Some invalid local-time DATEs will throw an error on conversion. There is no 2:00 AM on March 11th, 2007 in most of the US, for example. Add exceptions to handle that situation in convert_tz().
• Some local times have two instances. There are two 1:30 AMs on November 4th, 2007. An additional IN OUT parameter to convert_tz could be used to distinguish between the two.
• If all of your source or destination time zones for a particular package are identical, try creating global time zone consts.
• This TIMEZONE functionality is mostly new with Oracle 9i. Oracle has recently (as of the time of this writing) released patches specifically to address the new DST rules. As long as Oracle keeps patching for new DST rules, the above code will work.
• I tested on 10g and 9i. I noticed that 9i recognized the TZD 'GMT' but not 'UTC'. Something to keep in mind if you're implementing this on 9i.