Working With Oracle9i TIMESTAMP Datatypes

If you already explored new features of Oracle9i, you probably noticed some important additions and improvements in globalization support (and the appearance of this term itself, for that matter.) Oracle was always good at NLS (National Language Support), but in today's wired world language diversity is not the only thing to keep in mind when developing applications that are supposed to be accessed from anywhere in the world 24 hours a day, 7 days a week. The other important part of globalization support is, of course, time. Even though the time is unidirectional, it's not uniform. In global context, 10:00am is meaningless unless you specify the time zone. But DATE data type has no support for time zones, and when it says 10:00am we generally have no idea in which time zone it was 10:00am at that time, and, more importantly, what time it was elsewhere. To deal with this problem, Oracle9i introduces the new data type: TIMESTAMP WITH [LOCAL] TIME ZONE, and a host of new functions to deal with this data type. This datatype holds not only the date and time components, but also the time zone component. Key difference between TIMESTAMP WITH LOCAL TIME ZONE (TSWLTZ for short) and TIMESTAMP WITH TIME ZONE (TSWTZ) is that the former only holds offset from (fixed) database time zone, while the latter holds original time zone of the timestamp. Key benefit of the TSWLTZ is that it is automatically converted to the session time zone whenever retrieved. You can set the session time zone with

ALTER SESSION SET TIME_ZONE='SHH:MM|region name';

command, where S is offset sign (+ or -), HH:MM is offset in hours and minutes, and "region name" is zone name, like US/Eastern (to see all zone names Oracle supports, you can query the V$TIMEZONE_NAMES dictionary view.) You can use either of these formats for time zone specification, but not both simultaneously. However, once the database time zone is set (with ALTER DATABASE SET TIME_ZONE command) and you have defined single table with a TSWLTZ column in the database, you're locked to that time zone - Oracle won't let you change the database time zone because this will implicitly alter all TSWLTZ columns data - remember that they only hold the offset from the database time zone? If you will change the time zone, TSWLTZ will yeld different timestamps because it will add or substract the offset from new database time zone, not the one that was in effect when you created the row, and this will result in wrong data being returned. So if you attempt to alter the database time zone with at least one column of TSWLTZ type defined in the database, Oracle will throw this rather misguiding error (misguiding, because SET TIME_ZONE is pretty legal option, just not under these conditions, while the message suggests that you made a typo):

ORA-02231: missing or invalid option to ALTER DATABASE

Worse yet, TSWLTZ data type is not suited well for distributed systems where several databases around the world serve the application: each database will probably have its own time zone and TSWLTZ data in these databases will not be interchangeable between them. TSWTZ is free from this effect, because original time zone is always known. But then again, you certainly would want to present that data to users residing in different time zones and you would want to show what time it was in THEIR time zone, won't you? Oracle knew about that, and they extended the Datetime Expression syntax with AT clause to allow you to convert TSWTZ to different time zones easily:

datetime AT {LOCAL|TIME ZONE 'SHH:MM|region name')

AT LOCAL converts the datetime value to current session time zone, while AT TIME ZONE 'SHH:MM|region name' converts it to any arbitrary zone of your liking. This syntax is accepted in both SQL and PL/SQL, so you might do both

SELECT atimestamp AT LOCAL FROM mytable;

and

DECLARE
  t1 TIMESTAMP WITH TIME ZONE;
BEGIN
  t1 := :sometimestamp AT TIME ZONE '-08:00';
END;

Let's see AT clause in action:

SQL> SELECT TO_CHAR(TO_TIMESTAMP_TZ('2003-01-01 10:00 US/Eastern',
  2  				    'YYYY-MM-DD HH24:MI TZR'),
  3  		    'YYYY-MM-DD HH24:MI TZH:TZM') "US/Eastern",
  4  	    TO_CHAR(TO_TIMESTAMP_TZ('2003-01-01 10:00 US/Eastern',
  5  			    'YYYY-MM-DD HH24:MI TZR') AT TIME ZONE 'US/Pacific',
  6  		    'YYYY-MM-DD HH24:MI TZH:TZM') "US/Pacific"
  7    FROM SYS.DUAL;

US/Eastern              US/Pacific                                              
----------------------- -----------------------                                 
2003-01-01 10:00 -05:00 2003-01-01 08:00 -07:00                                 

So we see that 10:00am Eastern time was 08:00am Pacific time on January 1st, 2003. AT clause makes conversions between time zones a snap, so you can easily present your users with comfortable dates and times in their own time zones and they won't have to do this rather complex datetime math themselves (do you remember all time zone offsets for all time zones? Chances are, your users don't, too.)

Actually, using time zone names rather than offsets has additional benefit: if you use them, Oracle will employ its built-in time zone dictionary and will take into account any daylight savings that may be in effect in particular time zone at particular date and correct the offset accordingly. For example,

SQL> SELECT TO_CHAR(
  2         TO_TIMESTAMP_TZ('2003-01-01 10:00 Europe/Moscow',
  3                         'YYYY-MM-DD HH24:MI TZR'),
  4  	      'YYYY-MM-DD HH24:MI TZH:TZM') 
  5         "Daylight savings in effect" FROM SYS.DUAL;

Daylight savings in effect                                                      
----------------------------------------                                        
2003-01-01 10:00 +03:00                                                         

SQL> SELECT TO_CHAR(
  2  	      TO_TIMESTAMP_TZ('2003-06-01 10:00 Europe/Moscow',
  3                         'YYYY-MM-DD HH24:MI TZR'),
  4  	     'YYYY-MM-DD HH24:MI TZH:TZM')
  5        "No daylight savings" FROM SYS.DUAL;

No daylight savings                                                             
----------------------------------------                                        
2003-06-01 10:00 +04:00                                                         

Note that Oracle correctly recognized "winter time" on January 1st, 2003 and returned correct offset +03:00 for that timestamp, while the same time on June 1st, 2003 has offset of +04:00. With explicit offsets used for setting session or database time zone this is not possible.

To support the new TIMESTAMP types, Oracle also added a host of new functions and support for new literal specifications, which help you to instantiate and convert timestamps to and from strings (TO_TIMESTAMP[_TZ], extended TO_CHAR with new format specifiers), getting current timestamp in either database or session time zone (SYSTIMESTAMP and CURRENT_TIMESTAMP), finding out database or session time zone (DBTIMEZONE and SESSIONTIMEZONE), extracting time zone offset from zone abbreviation or region name (TZ_OFFSET), converting any TSWTZ to UTC (SYS_EXTRACT_UTC), converting DATE values to TSWTZ values (FROM_TZ), and powerful EXTRACT(datetime) function, which is part of the ANSI SQL standard and allows you to extract individual fields from datetime values:

EXTRACT( {YEAR|MONTH|DAY|HOUR|MINUTE|SECOND|TIMEZONE_HOUR|TIMEZONE_MINUTE|
          TIMEZONE_REGION|TIMEZONE_ABBR} FROM datetime_value_expression )

One important thing to remember about EXTRACT with regard to TSWTZ values is that returned values are always in UTC (Universal Coordinated Time, formerly known as GMT, or Greenwich Mean Time), no matter the original time zone.

I won't delve deeper into the subject now, as there's a lot of really helpful reading available on it, including indispensable Oracle manuals (SQL Reference in particular) and Steven Feuerstein's excellent PL/SQL Programming, 3rd Edition (I was lucky to win a copy of this book, and I don't regret the time spent reading it.) There's much more to the TIMESTAMPs, including their increased resolution (to the fractions of second), new format specifiers for fractional seconds, time zone offsets, region names and abbreviations, etc. If you think globally (and I bet you do), you will surely use these exciting new Oracle9i data types in your projects and your users all around the world will be pleased.