422,530 Members | 1,130 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Convertion of Time Zones in Oracle

amitpatel66
Expert 100+
P: 2,368
Hi All,

Find below some useful information about Time Zone Conversion in oracle. Hope this would be helpful for many of them since all the real time projects that we work in follow different time zones (EST,PST etc) and you might well need to convert them to something specific as per your requirements:

Expand|Select|Wrap|Line Numbers
  1. insert into dates values(6, to_date('09/20/05 23:15', 'MM/DD/YY HH24:MI'));
  2.  
  3. --The contents of the table now look like this:
  4.  
  5. 1 09/14/05, 21:08
  6. 2 09/27/05, 00:00
  7. 3 10/02/05, 22:05
  8. 4 09/01/05, 17:01
  9. 5 09/12/05, 14:30
  10. 6 09/20/05, 23:15
  11.  
  12.  
Changing Time Zones

The date format in Oracle does not contain time zone information, but the database does. To find out the time zone set, execute this query:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT dbtimezone FROM dual;
  3.  
  4. DBTIME
  5. ——
  6. -04:00
  7.  
The time zone can be updated with the command:

Expand|Select|Wrap|Line Numbers
  1. ALTER database SET TIME_ZONE = '-05:00';
  2.  
where you can specify the offset from Greenwich mean time or a valid time zone from the list in the v$timezone_names view. Note that this is one of the few of the ‘v$’ views which are plural.

Switching Time Zones

The function new_time is used to convert a time to different time zones. To illustrate this we’ll look at entry 5 from the dates file.

Expand|Select|Wrap|Line Numbers
  1. SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') e_date FROM dates WHERE entry=5;
  2.  
  3. entry       e_date    
  4. 5             09/12/05 02:30 PM
  5.  
  6.  
This database is in US Eastern time but we want to display the time in US Central.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT entry, to_char(new_time(entry_date, 'EST', 'CST'), 'MM/DD/YY HH:MI AM') e_Date FROM dates WHERE entry=5;
  3.  
  4. entry       e_date
  5. 5             09/12/05 01:30 PM
  6.  
  7.  
Here we clearly see the time converted to Central. Note that the new_time function is performed on the date field, not on the to_char.

Now let’s grab this time in Pacific time:

Expand|Select|Wrap|Line Numbers
  1. SELECT entry, to_char(new_time(entry_date, 'EST', 'PST'), 'MM/DD/YY HH:MI AM') e_date FROM dates WHERE entry=5;
  2.  
  3. entry       e_date
  4. 5            09/12/05 11:30 AM
  5.  
  6.  

Now we see not only the time converted, but also the time of day has gone from PM to AM.

Now let’s take a look at entry 6:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') e_date FROM dates WHERE entry=6;
  3.  
  4. entry       e_date
  5. 6             09/20/05 11:15 PM
  6.  

We’ll again assume this timestamp is in US Eastern time, but let’s convert it this time to Greenwich Mean Time.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT entry, to_char(new_time(entry_date, 'EST', 'GMT'), 'MM/DD/YY HH:MI AM') e_date FROM dates WHERE entry=6;
  3.  
  4. entry       e_date
  5. 6             09/21/05 04:15 AM
  6.  
  7.  
This shows not only the change in hours, but that the date of this entry is displayed properly for its time zone. Of course the new_time function can be used on inserts in the same way. This is useful if you are allowing input from people in different geographical regions.

Here we convert an entry made in Pacific Time to Eastern:


Code:
Expand|Select|Wrap|Line Numbers
  1.  
  2. INSERT INTO dates
  3. VALUES (7,
  4. new_time(to_date(’09/22/05 10:28 AM’, ‘MM/DD/YY HH:MI AM’), ‘PST’, ‘EST’));
  5.  
  6.  
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT entry, to_char(entry_date, ‘MM/DD/YY HH:MI AM’) e_date FROM dates WHERE entry=7;
  3.  
  4. entry       e_date
  5. 7             09/22/05 01:28 PM
  6.  
So we have converted 10:28 AM Pacific to 1:28 PM Eastern so all our entries in the table are consistent. Of course when performing the insert we need to put the to_date function within the new_time function so the text string is converted to a date format before we try to convert it.
Dec 30 '08 #1
Share this Article
Share on Google+