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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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,021 views