Re: How to arrive at PST or PDT for a date?
- From: "Anurag Varma" <avoracle@xxxxxxxxx>
- To: gkatteri@xxxxxxxx
- Date: Mon, 12 Mar 2007 21:45:59 -0400
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@xxxxxxxx> 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
Other related posts:
