ArsDigita Archives
 
 
   
 
spacer

Timezones

Part of an article on Building a Multilingual Web Service Using the ACS, by John Lowry (lowry@arsdigita.com)

ArsDigita : ArsDigita Systems Journal : One article

previous chapter

Timezones

 Although timezones are not required for a multilingual web site, we cover this topic because, more often than not, a site that is available in more than one language will need to understand the concept of timezones.

Consider a user in Paris who submits a web page that includes a date entry field. The user enters a date that corresponds to his local timezone. However, the database is configured to return Universal Time (UTC) from the sysdate function. Should the user's date be stored in local time or UTC?

It is generally easier to store dates in local time to avoid having to convert to and from UTC whenever you fetch or store a date in the database. However, it is sometimes necessary to do this conversion. Imagine that we have a ticket tracking application that allows users to impose a time-to-fix date on each ticket. We might have a scheduled procedure that sends out an alert if the deadline on a ticket has passed. We would need to know the time difference between the ticket deadline in the user's local timezone and the database time which is UTC.

Oracle's built-in support for timezones is not sufficient. It provides the NEW_TIME SQL function that converts between timezones found in the US. NEW_TIME, however, has no understanding of changes in the time zone for summer time or non-US timezones. Therefore we provide a PL/SQL function that does this job. Below, we show the function to convert a local time to UTC (the function which does the reverse, UTC to local time, is very similar). 

CREATE or REPLACE FUNCTION lc_time_local_to_utc
   (local_time IN date,
    this_tz IN varchar2) 
RETURN date IS utc_time date;
BEGIN
   SELECT local_time - utc_offset 
     INTO utc_time 
     FROM tz_data 
    WHERE tz = this_tz 
      AND local_time between local_start and local_end 
      AND rownum = 1; 
   RETURN (utc_time);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
   RETURN(local_time);
END;
This function needs to know the time difference between local time and UTC. It is not enough to just know the UTC offset of a timezone. A user located in Europe/Paris may be in one of two possible timezones depending on the time of the year: Central European Time (CET) or Central European Summer Time (CEST). We store the mapping between location, date and timezone in the tz_data table. This table is populated with data that is provided by the public domain time zone database.

The query below, for example, shows the date of recent clock changes for users in Paris. The UTC offset is measured in fractions of a day which simplifies date arithmetic within Oracle. 

SQL> SELECT timezone,
            local_start,
            local_end,
            ROUND(utc_offset * 24)
       FROM tz_data 
      WHERE tz = 'Europe/Paris'
        AND local_start > to_date('1999-01-01', 'YYYY-MM-DD') 
        AND local_end < to_date('2000-12-31', 'YYYY-MM-DD')  
   ORDER BY local_start;

TIMEZONE   LOCAL_STAR LOCAL_END  ROUND(UTC_OFFSET*24)
---------- ---------- ---------- --------------------
CEST	   1999-03-28 1999-10-31		    2
CET	   1999-10-31 2000-03-26		    1
CEST	   2000-03-26 2000-10-29		    2

More information

Oracle NEW_TIME function http://oradoc.photo.net/ora816/server.816/a76989/functi54.htm#78068
Public domain time zone database http://www.twinsun.com/tz/tz-link.htm
Article on using Java to handle timezones http://www.javaworld.com/javaworld/jw-02-1999/jw-02-internationalize.html
Java TimeZone class http://java.sun.com/products/jdk/1.2/docs/api/java/util/TimeZone.html
Bulletin Board discussion of timezones in the ACS http://www.arsdigita.com/bboard/q-and-a-fetch-msg.tcl?msg_id=0003Zr
asj-editors@arsdigita.com
spacer