Converting Time Zones in Oracle

In my previous article Oracle, SQL, Dates and Timestamps I talked about inserting, selecting and comparing dates in Oracle. Now I want to cover some functionality for converting between time zones.

We’ll use the table in the form specified in the previous article, but insert one more row:

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') FROM dates WHERE entry=5;

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') FROM dates WHERE entry=5;

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') FROM dates WHERE entry=5;

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') FROM dates WHERE entry=6;

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') FROM dates WHERE entry=6;

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:

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') FROM dates WHERE entry=7;

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.

oracle, sql, dba, database administration, database development

20 thoughts on “Converting Time Zones in Oracle”

  1. Pingback: Life After Coffee » Performing Math on Oracle Dates

  3. hi, how would i be able display a date and time with time zone format. thanks.

    e.g.
    17-Mar-2006 14:30:00 EST

  4. Pingback: Life After Coffee » Oracle Timestamp With Time Zone

  5. The query
    SELECT entry, to_char(new_time(entry_date, ‘EST’, ‘GMT’), ‘MM/DD/YY HH:MI AM’) FROM dates WHERE entry=6;
    in the example above returns correct GMT time only when daylight saving is not in effect. What do to do if daylight saving is in effect?
    Any suggestions would be of great help.

    Thanks,
    Niks.

  6. Niks, I think you would have to write a custom procedure for this which would use EST for Eastern and EDT for dayligt savings.

    You would probably want to set up a table which would contain the begin and end dates for all the years you’re concerned with then use BETWEEN to check it. If it is BETWEEN the EDT start and end dates you would return EDT for the from time zone, if not you would return EST.

    Sorry it’s not an easier answer, but as far as I can tell Oracle doesn’t provide an easy way to do this.

    Jon

  7. I need to convert from dbtimezone to sessiontimezone, however
    NEW_TIME(date,dbtimezone,sessiontimezone) doesn’t work – does this function only work with timezone abreviations? Anyway to simply convert dbtimezone, sessiontimezone to the correct abbreviation?

  8. Marty, I had troubles reproducing what you’re trying to do (there’s something funny with timezones in my databases… I’ll have to fix that later.)

    I did turn up a couple articles elsewhere which may help you. Sounds like you’ll want to do something like

    select
    (substr(tz_offset(sessiontimezone),1,1)||1)*to_dsinterval(‘0 ‘ || substr(tz_offset(sessiontimezone), 2, 5) || ‘:00’) –
    (substr(tz_offset(dbtimezone),1,1)||1)*to_dsinterval(‘0 ‘ || substr(tz_offset(dbtimezone), 2, 5) || ‘:00’)
    from dual;

    It’s a bit convoluted, but basically this gets the sessiontimezone into an acceptable date interval and subtracts the dbtimezone. The result is a date interval which you would add to a date in the session timezone to convert it to the system timezone.

    Here are two other articles I turned up on the topic which may help:

    From AskTom
    From dbasupport.com

    Hope this helps. This really does emphasize how painful it is to deal with Oracle dates.

  9. Hi,

    The article presented is really very good. I do not know the time zone corresponding to CET. My query

    select to_char(new_time(sysdate, ‘CET’, ‘GMT’)) from dual

    throws me error that the time zone is not valid.

    Request you kindly let me know the valid time zone.

    Thanks and Regards,
    Vasant
    vasantmv@gmail.com

  10. Vasant,

    It turns out new_time will only accept a handful of US timezones. It’s probably possible to do some tricks with the tz_offset and the numtodsinterval functions but the easiest thing to do in most cases is to just add/subtract the appropriate number of hours for your timezone.

    For example, in Eastern/US I would add 5 hours to find out the GMT value like this:

    select sysdate + 5/24 from dual;

    Hope this helps.

    Jon

  11. Useful article, thanks. Note that a database restart is required in order for the alter database set TIME_ZONE= to take effect.

    Sergio

  12. I have a database that stores date values in GMT, and currently the DBTIMEZONE is set to GMT as well, if I change the DBTIMEZONE, will dates be displayed in the GMT (stored) format, or will they be displayed in the DBTIMEZONE offset from GMT?

    Thansk!
    Jeff

  13. Jeff,

    You should check the docs for your version of Oracle. I believe this varies, but my guess is your dates will continue to be displayed in their originally inserted GMT.

  14. Vasant,
    There is another way you can check..
    You need to alter your session.. Here is an example to convert from CET to EST..

    alter session set nls_date_format = ‘DD.MM.YYYY HH24:MI:SS’;
    alter session set NLS_TIMESTAMP_FORMAT = ‘DD.MM.YYYY HH24:MI:SSXFF’;
    alter session set time_zone = ‘US/Eastern’;

    select id,time1, to_date(to_char((from_tz(to_timestamp(time1, ‘dd-mm-yyyy hh24:mi:ss’) ,’Europe/BRUSSELS’)
    at time zone ‘US/Eastern’),’mm-dd-yyyy hh24:mi:ss’),’mm-dd-yyyy hh24:mi:ss’) from Table1

    This will also takes care of Daylight Savings.

    Hope this helps.

    Prasad.

  16. I’ve got a problem with java that extends to dbtimezone. If the dbtimezone defaults to a given timezone (ostensibly when created if not explicitly stated it’s SUPPOSED to get the OS timezone and I can’t see that ANYWHERE in the registry of my windows machine where it’s set to GMT -7:00 (we’re in GMT -6:00)) when it’s created, and its default in this case is off by an hour compared to the server it’s on, the java I’m calling converts the milliseconds of a given date according to the offset between the dbtimezone and the server setting)… When I change the dbtimezone to match the server and restart the instance it STILL acts as if it’s in the old time zone.

  17. we have used this two function:

    (each schema has its own time_zone stored in sys_values table)

    create or replace
    function LOC_DATETIME(pDATE in date) return date is
    vTimeZone varchar2(50);
    vDbZone varchar2(50);
    begin
    select TIME_ZONE into vTimeZone from sys_values;
    vDbZone := ‘Europe/BRUSSELS’;
    return
    to_date(to_char(from_tz(TO_TIMESTAMP(to_char(pDATE,’mm-dd-yyyy hh24:mi:ss’),
    ‘mm-dd-yyyy hh24:mi:ss’),vDbZone) at time zone vTimeZone,’mm-dd-yyyy hh24:mi:ss’), ‘mm-dd-yyyy hh24:mi:ss’);
    end;
    /
    create or replace
    function DB_DATETIME(pDATE in date) return date is
    vTimeZone varchar2(50);
    vDbZone varchar2(50);
    begin
    select TIME_ZONE into vTimeZone from sys_values;
    vDbZone := ‘Europe/BRUSSELS’;
    return
    to_date(to_char(from_tz(TO_TIMESTAMP(to_char(pDATE,’mm-dd-yyyy hh24:mi:ss’),
    ‘mm-dd-yyyy hh24:mi:ss’),vTimeZone) at time zone vDbZone,’mm-dd-yyyy hh24:mi:ss’), ‘mm-dd-yyyy hh24:mi:ss’);
    end;
    /

    it works

  20. Hi All,

    I am a newbie in this domain. And would like to know how I can convert GMT into other TimeZones?

    I have a timestamp in GMT and want to convert it to EST and MST, how I can use oracle to convert this?

    Will Appreciate your response.

    Thanks,
    Sunil

Leave a Reply

Your email address will not be published. Required fields are marked *