Convertion of Time Zones in Oracle

[amitpatel66]

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:

insert into dates values(6, to_date('09/20/05 23:15', 'MM/DD/YY HH24:MI'));
 
--The contents of the table now look like this:
 
1 09/14/05, 21:08
2 09/27/05, 00:00
3 10/02/05, 22:05
4 09/01/05, 17:01
5 09/12/05, 14:30
6 09/20/05, 23:15
 
 

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:

 
SELECT dbtimezone FROM dual;
 
DBTIME
——
-04:00
 

The time zone can be updated with the command:

ALTER database SET TIME_ZONE = '-05:00';
 

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.

SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') e_date FROM dates WHERE entry=5;
 
entry       e_date    
5             09/12/05 02:30 PM
 
 

This database is in US Eastern time but we want to display the time in US Central.

 
SELECT entry, to_char(new_time(entry_date, 'EST', 'CST'), 'MM/DD/YY HH:MI AM') e_Date FROM dates WHERE entry=5;
 
entry       e_date
5             09/12/05 01:30 PM
 
 

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:

SELECT entry, to_char(new_time(entry_date, 'EST', 'PST'), 'MM/DD/YY HH:MI AM') e_date FROM dates WHERE entry=5;
 
entry       e_date
5            09/12/05 11:30 AM
 
 

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:

 
SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') e_date FROM dates WHERE entry=6;
 
entry       e_date
6             09/20/05 11:15 PM
 

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

 
SELECT entry, to_char(new_time(entry_date, 'EST', 'GMT'), 'MM/DD/YY HH:MI AM') e_date FROM dates WHERE entry=6;
 
entry       e_date
6             09/21/05 04:15 AM
 
 

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:

 
INSERT INTO dates
VALUES (7,
new_time(to_date(’09/22/05 10:28 AM’, ‘MM/DD/YY HH:MI AM’), ‘PST’, ‘EST’));
 
 

 
SELECT entry, to_char(entry_date, ‘MM/DD/YY HH:MI AM’) e_date FROM dates WHERE entry=7;
 
entry       e_date
7             09/22/05 01:28 PM
 

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.