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;
/