Re: How to arrive at PST or PDT for a date?

From: Anurag Varma <avoracle_at_gmail.com>
Date: Mon, 12 Mar 2007 21:45:59 -0400
Message-ID: <39f68240703121845y5f0448a3n76ba9a4a646384da@mail.gmail.com>

Govindan,

I don't do well with timezone functions myself .. but here is a stab at your question:

ORA92> select sessiontimezone from dual;

SESSIONTIMEZONE

-04:00

Session timezone set to -04:00 .. which is the offset. Does not really
tell us whether its US/Eastern or what...

ORA92> select dbtimezone from dual;

DBTIME

+00:00

dbtimezone does not appear to be set in this database .. default to GMT

ORA92> select cast(sysdate as timestamp) from dual;

CAST(SYSDATEASTIMESTAMP)

12-MAR-07 09.05.32.000000 PM ORA92> select from_tz(cast(sysdate as timestamp), sessiontimezone) from dual;

FROM_TZ(CAST(SYSDATEASTIMESTAMP),SESSIONTIMEZONE)

12-MAR-07 09.08.20.000000 PM -04:00

Here we see the offset information being shown .. same as what sessiontimezone set to

ORA92> select from_tz(cast(sysdate as timestamp), 'US/Eastern') from dual;

FROM_TZ(CAST(SYSDATEASTIMESTAM

12-MAR-07 09.08.53.000000 PM US/EASTERN

Here we specify the proper timezone region (I'm on US/Eastern)

ORA92> select to_char(from_tz(cast(sysdate as timestamp), 'US/Eastern'),'TZD') from dual;

TO_CHAR(FROM_TZ(CAST(SYSDATEAS

EDT

to_char to extract the timezone abbreviation

ORA92> select to_char(from_tz(cast(sysdate - 10 as timestamp), 'US/Eastern'),'TZD') from dual;

TO_CHAR(FROM_TZ(CAST(SYSDATE-1

EST ORA92> select extract(timezone_abbr from from_tz(cast(sysdate as timestamp), 'US/Eastern')) from dual;

EXTRACT(TI

EDT

or we can use the extract function to extract the timezone abbreviation

ORA92> alter session set time_zone='US/Eastern';

Session altered.

We set the proper time_zone here ... proper because offset does not relay the actual time zone

ORA92> select from_tz(cast(sysdate as timestamp), sessiontimezone) from dual;

FROM_TZ(CAST(SYSDATEASTIMESTAMP),SESSIONTIMEZONE)

12-MAR-07 09.10.25.000000 PM US/EASTERN

This now session setting is now reflected

ORA92> select extract(timezone_abbr from from_tz(cast(sysdate as timestamp), sessiontimezone)) from dual;

EXTRACT(TI

EDT Hope this helps ... You might want to experiment more on these lines ....

Anurag

On 3/12/07, Govindan K. <gkatteri_at_inbox.lv> wrote:

>
>
> Hello
>
> This is on 9iRel2 Solaris.
>
> I have a date value. But i would need to find out if the date belongs to
> PST or PDT. PDT if between 11-mar / nov 4th; I donot want to hardcode ; I
> prefer to use some Timezone function which i can rely on for future updates
> if any.
>
> SQL>column sessiontimezone format A10 wrap;
> SQL>select change_id_ , create_date
> 2 , cdate(create_date)
> 3 , sessiontimezone
> 4 , TO_CHAR(cdate(create_date), 'DD-MON-YYYY HH24:MI:SS') to_charr
> 5 from chg_change where change_id_ like '%3261%'
> 6 /
>
> CHANGE_ID_ CREATE_DATE CDATE(CREATE_DATE) SESSIONTIM TO_CHARR
> --------------- ----------- -------------------- ----------
> --------------------
> CHG000000003261 1033750736 04-OCT-2002 09:58:56 -07:00 04-OCT-2002
> 09:58:56
> CHG000000013261 1081461511 08-APR-2004 14:58:31 -07:00 08-APR-2004
> 14:58:31
> CHG000000023261 1129660641 18-OCT-2005 11:37:21 -07:00 18-OCT-2005
> 11:37:21
> CHG000000032610 1172769011 01-MAR-2007 10:10:11 -07:00 01-MAR-2007
> 10:10:11
> CHG000000032611 1172769657 01-MAR-2007 10:20:57 -07:00 01-MAR-2007
> 10:20:57
> CHG000000032612 1172770327 01-MAR-2007 10:32:07 -07:00 01-MAR-2007
> 10:32:07
> CHG000000032614 1172772261 01-MAR-2007 11:04:21 -07:00 01-MAR-2007
> 11:04:21
> CHG000000032615 1172772456 01-MAR-2007 11:07:36 -07:00 01-MAR-2007
> 11:07:36
> CHG000000032616 1172772877 01-MAR-2007 11:14:37 -07:00 01-MAR-2007
> 11:14:37
> CHG000000032617 1172773199 01-MAR-2007 11:19:59 -07:00 01-MAR-2007
> 11:19:59
> CHG000000032619 1172777265 01-MAR-2007 12:27:45 -07:00 01-MAR-2007
> 12:27:45
> CHG000000032618 1172776231 01-MAR-2007 12:10:31 -07:00 01-MAR-2007
> 12:10:31
> CHG000000032613 1172771579 01-MAR-2007 10:52:59 -07:00 01-MAR-2007
> 10:52:59
>
> 13 rows selected.
>
> SQL>
>
> In other words i wound need to know if a date falls outside DayLight
> Savings. I would appreciate any help.
>
> Thanks
> Govindan
>
>
>
>
> __________
> Advertisement:
>    - Inbox.lv – bezmaksas 2.5 GB (2500 MB) liela pastkastite
>  - Anti-Virus un Anti-Spam aizsardziba
>  - REGISTRACIJA
> <http://ads.inbox.lv/htmlclick.php?bannerID=6168&dest=http%3A%2F%2Fwww.inbox.lv%2Fhorde%2Fimp%2Fsignup.php%3Flanguage%3Dlv_LV>
>   [image: www.inbox.lv]
> <http://ads.inbox.lv/htmlclick.php?bannerID=6168&dest=http%3A%2F%2Fwww.inbox.lv>
>

-- 
Anurag Varma

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 12 2007 - 20:45:59 CDT