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!

determining PST time from SYSTIMESTAMP

user10936714Apr 8 2009 — edited Apr 8 2009
Thanks to everyone who answered by previous question about SYSTIMESTAMP.

Here's a PL/SQL function I wrote to convert the current time to PST and return it as a DATE value. The idea is that it should work regardless of what the database's timezone is set to.

Any critiques are welcome - efficiency, style, whatever. In particular, is there a way to avoid the conversion to a VARCHAR2? Is there a system supplied function I am overlooking?

Thanks again!
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;
/
This post has been answered by JustinCave on Apr 8 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 6 2009
Added on Apr 8 2009
2 comments
1,090 views