Re: time zone related problem

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 18 Sep 2006 13:28:06 -0700
Message-ID: <1158611285.479853.160770@e3g2000cwe.googlegroups.com>

uday wrote:
> I have to convert given time from "EST" to any of the following
> timezones
> Poland
> Etc/GMT-3
> ACT
> Etc/GMT-2
> Etc/GMT-10
> GMT
> Etc/GMT-9
> Etc/GMT-1
> CET
> CST
> Etc/GMT+6
> US/Pacific
> EST
> PST
> AGT
> Hongkong
>
> I cannot use java stored-procedure {the JAVA support is not installed
> at the production server}
>
> I have to use it from within oracle tried using new_time but
> not-supporting all above timezone
>
>
> some-body please help

SELECT * FROM V$TIMEZONE_NAMES will show you all time zone names Oracle supports. All time zone names you listed are present there (at least in 10.1.0.5.) To convert a time stamp to another time zone you can use this syntax:

SELECT :timestamp_var AT TIME ZONE 'timezone-name' FROM DUAL;

For example,

SQL> ALTER SESSION SET TIME_ZONE='EST'; Session altered.

SQL> SELECT current_timestamp AT TIME ZONE 'Poland' FROM DUAL;

CURRENT_TIMESTAMPATTIMEZONE'POLAND'

---

18-SEP-06 10.24.48.577559 PM POLAND SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP

---

18-SEP-06 04.24.49.489085 PM EST Note that the returned value will be TIMESTAMP WITH TIME ZONE, but you can convert it to a DATE easily.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Mon Sep 18 2006 - 15:28:06 CDT