Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Write Time Zone Aware Code in Oracle : Page 2

Oracle 9i introduced data types and support to handle time zones. Use these data types to make your Oracle code time zone aware.


advertisement

WEBINAR: On-Demand

Unleash Your DevOps Strategy by Synchronizing Application and Database Changes REGISTER >

The Two Time-Zoned Data Types

Oracle provides two distinct time zone aware data types, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. From the names, you might think they're almost the same, but they're actually stored and processed quite differently.

When you use a TIMESTAMP WITH TIME ZONE data type, time zone information is stored in the database along with the month, day, year, hours, minutes, seconds, and fractional seconds. Whenever you retrieve that data, the time zone that it was stored with comes along. If you don't specify time zone information when you store the data, then the Oracle client pulls the information from your session's time zone (See Sidebar 2. Displaying Time Zone Information).

In contrast, when you use a TIMESTAMP WITH LOCAL TIME ZONE data type, Oracle does not store any time zone information. Instead, it converts the timestamp to a "baseline" time zone that it uses to store all the TIMESTAMP WITH LOCAL TIME ZONE data in the database. When you retrieve TIMESTAMP WITH LOCAL TIME ZONE data from the database, Oracle converts it on the fly from the "baseline" time zone to the time zone of your current session.



This "baseline" time zone is what Oracle refers to as the database time zone. This "database time zone" has absolutely nothing to do with the time zone the database is actually in. It is used only for storing TIMESTAMP WITH LOCAL TIME ZONE data. When you run the SYSTIMESTAMP function, for example, you get back a TIMESTAMP WITH TIME ZONE that is in the time zone of the database server, not in the "database time zone". (See Sidebar 3. Time Zone Names for more details.)

In fact, Oracle recommends that you set the "database time zone" to +00:00, the default, in order to speed up the on-the-fly conversions that it does with TIMESTAMP WITH LOCAL TIME ZONE data.

Explore the difference between TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE data by going through the following example.

Start by creating a table that has fields with all three data types:

SQL> create table tmp_tst_tz 2 (ts timestamp, 3 tstz timestamp with time zone, 4 tsltz timestamp with local time zone); Table created.

Assume that your database server's actual time zone is UTC -05:00. Then SYSTIMESTAMP will return the current time on the database server, with a time-zone displacement of -05:00 (see Sidebar 1). Start out by setting your client session's time zone (see Sidebar 2) to match:

SQL> alter session set time_zone='-05:00'; Session altered.

Now insert SYSTIMESTAMP into all three columns in the test table (see Sidebar 2 for more information on SYSTIMESTAMP):

SQL> insert into tmp_tst_tz values (systimestamp, systimestamp, systimestamp) ; 1 row created. SQL> commit; Commit complete.

Before querying the table, make sure your default timestamp formats are set, so that the data will be easy to read (see Sidebar 2 for more information):

SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='mm/dd/yyyy hh24:mi:ssxff TZH:TZM' ; Session altered. SQL> alter session set NLS_TIMESTAMP_FORMAT='mm/dd/yyyy hh24:mi:ssxff' ;

Now query the table:

SQL> select * from tmp_tst_tz ; TS TSTZ TSLTZ --------------------------- ----------------------------------- ----------------------------------- 01/29/2006 21:46:29.874272 01/29/2006 21:46:29.874272 -05:00 01/29/2006 21:46:29.874272 1 row selected.

So far, so good. Note that the TIMESTAMP WITH LOCAL TIME ZONE data (in column TSLTZ), like the TIMESTAMP data, doesn't have any time zone information associated with it.

Now, to see the differences between TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data, change your session time zone (see Sidebar 2 for more information on your session time zone). Go back to Istanbul, which is seven hours ahead of UTC -05:00. Set the time zone to 'Turkey' (see Sidebar 3) and select from the table:

SQL> alter session set time_zone='Turkey' ; Session altered. SQL> select * from tmp_tst_tz ; SQL> select * from tmp_nr_tstz; TS TSTZ TSLTZ ----------------------------------- ----------------------------------- ----------------------------------- 01/29/2006 21:46:29.874272 01/29/2006 21:46:29.874272 -05:00 01/30/2006 04:46:29.874272 1 row selected.

The value Oracle returns in the TSLTZ column has changed! When your session retrieves the TIME STAMP WITH LOCAL TIME ZONE data from the database, Oracle automatically converts it on the fly to the equivalent time in your local time zone. At 21:46 in Toronto, it's 04:46 in Istanbul; so that's the time displayed when 21:46 is entered in Toronto time and retrieved in Istanbul time.

Note also that the TIMESTAMP data hasn't changed. This is an excellent illustration of the importance of using time zones. To return to the call center example from the article introduction, if a call center in Toronto had simply logged a call at 21:46 in a TIMESTAMP column, then an operator in Istanbul following up on that call would think the call had taken place seven hours before it actually did. If the application developer had used TIMESTAMP WITH TIME ZONE, the Istanbul operator could see that the call had taken place at 21:46 EST; if the application developer had used TIMESTAMP WITH LOCAL TIME ZONE, then the operator would see that the call had taken place at 4:46 AM local time.

Of course, application developers can choose TIMESTAMP WITH TIME ZONE and perform their own conversions. The easiest way to do this is to use the "AT TIME ZONE" syntax. This lets you take a TIMESTAMP WITH TIME ZONE and display what it would be in another time zone:

SQL> select tstz at time zone 'Turkey', tsltz from tmp_nr_tstz ; TSTZATTIMEZONE'TURKEY' TSLTZ --------------------------------------------------------------------------- ----------------------------------- 01/30/2006 04:46:29.874272 +02:00 01/30/2006 04:46:29.874272 1 row selected.

You can display a TIMESTAMP WITH TIME ZONE data type almost as though it were a TIMESTAMP WITH LOCAL TIME ZONE by combining the AT TIME ZONE syntax with the SESSIONTIMEZONE function:

SQL> select tstz at time zone sessiontimezone, tsltz from tmp_nr_tstz; TSTZATTIMEZONESESSIONTIMEZONE TSLTZ --------------------------------------------------------------------------- ----------------------------------- 01/30/2006 04:46:29.874272 +02:00 01/30/2006 04:46:29.874272 1 row selected.

The AT TIME ZONE syntax can be applied to any datetime column or expression.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date