Re: timezone conversions?

From: Maxim <mdemenko_at_gmail.com>
Date: Wed, 11 Mar 2009 02:40:29 -0700 (PDT)
Message-ID: <054f27db-2bcd-4476-999d-d87371ccc75b_at_z1g2000yqn.googlegroups.com>



On 11 Mrz., 10:36, "Bobby Z." <vladimir.zakharyc..._at_gmail.com> wrote:
> 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

Very useful, thanks

Best regards

Maxim Received on Wed Mar 11 2009 - 04:40:29 CDT

Original text of this message