Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SYSTIMESTAMP and PST again

user10936714Apr 9 2009 — edited Apr 15 2009
In a previous question I proposed the following function to determine the current time as represeted in PST:
CREATE OR REPLACE FUNCTION PST_SYSDATE RETURN DATE IS
  th NUMBER;
  tm NUMBER;
  now TIMESTAMP(6) WITH TIME ZONE;
  d  VARCHAR2(200);
BEGIN
  now := SYSTIMESTAMP;
  SELECT
    TO_CHAR(now, 'YYYY-MM-DD HH24:MI:SS'),
    EXTRACT(timezone_hour FROM now) TH,
    EXTRACT(timezone_minute FROM now) TM
  INTO d, th, tm
  FROM DUAL;
  
  RETURN TO_DATE(d, 'YYYY-MM-DD HH24:MI:SS') - ((th+tm/60+8)/24);  
END PST_SYSDATE;
/
I got some helpful replies which suggested that I use the expression:
CAST( SYSTIMESTAMP AT TIME ZONE TZ_OFFSET('PST') AS DATE)
However, my function is not agreeing with this expression:
SQL>  select SYSDATE, PST_SYSDATE,  CAST(SYSTIMESTAMP AT TIME ZONE TZ_OFFSET('PST') AS DATE) PST_DATE from dual;

SYSDATE             PST_SYSDATE         PST_DATE
------------------- ------------------- -------------------
04/09/2009 11:18:05 04/09/2009 08:18:05 04/09/2009 09:18:06
Note that I am in the Central Timezone and we are currently using CDT. 11 AM CDT is 8 AM PST not 9 AM PST.

So is there another expression which can do the same thing as my PST_SYSDATE function?

Thanks!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 13 2009
Added on Apr 9 2009
11 comments
5,688 views