Re: timezone conversions?

From: Bobby Z. <vladimir.zakharychev_at_gmail.com>
Date: Wed, 11 Mar 2009 02:36:24 -0700 (PDT)
Message-ID: <cf34481a-e3a3-4d0b-aa85-4286a5afec97_at_c11g2000yqj.googlegroups.com>

On Mar 10, 6:46 pm, steph <stepha..._at_yahoo.de> wrote:
> hello group,
>
> What's the easiest/preferred way to programmatically convert a
> timestamp value from one timezone to another? (i.e. subtract/add the
> necessary number of hours). I checked the documentation but
> unfortunately missed any one function to switch timezones.
>
> thanks,
> stephan

Use AT TIME ZONE clause, like this:

SQL> SELECT current_timestamp AT TIME ZONE 'America/New_York' NYT,   2 current_timestamp AT TIME ZONE 'Europe/Moscow' MSK   3 FROM SYS.DUAL
  4 /

NYT                                          MSK
--------------------------------------------
-------------------------------------------------

11.03.09 05:24:27,427000 AMERICA/NEW_YORK 11.03.09 12:24:27,427000 EUROPE/MOSCOW AT TIME ZONE, when used with standard zone names like in my example, takes daylight savings into account (uses built-in time zone dictionary, which Oracle upgrades from time to time via one-off patches.) You can also use AT LOCAL to convert timestamps with time zone to your local time zone:

SQL> SELECT TIMESTAMP'2009-03-11 01:00:00.00 Australia/Sydney' AT LOCAL FROM SYS.DUAL; TIMESTAMP'2009-03-1101:00:00.0

---

10.03.09 17:00:00,000000000 +03:00

Note that local time zone is your session time zone, not the database time zone:

SQL> ALTER SESSION SET time_zone='Europe/London';

Session altered

SQL> SELECT TIMESTAMP'2009-03-11 01:00:00.00 Australia/Sydney' AT LOCAL FROM SYS.DUAL; TIMESTAMP'2009-03-1101:00:00.0

---

10.03.09 14:00:00,000000000 EUROPE/LONDON Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Wed Mar 11 2009 - 04:36:24 CDT