Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: time zone related problem

Re: time zone related problem

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 18 Sep 2006 16:29:25 -0700
Message-ID: <1158622163.381880@bubbleator.drizzle.com>


Vladimir M. Zakharychev wrote:
> 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

My recommendation will likely be to use the FROM_TZ function. But I am still not convinced that I understand the OP's intent.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group
Received on Mon Sep 18 2006 - 18:29:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US