I have a client that has their oracle DB on a Unix machine with the
clock set to the PST timezone and observing daylight savings changes.
They're on US Pacific time.

My app needs to know the UTC date for it's transactions, so I thought I
had done that with this:
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS PST_DATE,
TO_CHAR(new_time(sysdate, 'PST', 'GMT'), 'YYYY/MM/DD HH24:MI:SS') AS
ZULU_DATE
FROM DUAL;

and I recently learned that new_time doesn't work when you're in
daylight savings. I then learned if I use PDT instead of PST, then it
works when you are currently in daylight savings.

My question is... I don't want to waste time writing code to check if
its currently daylight savings or not, etc, as I'm sure it's been done
before. I would think Oracle would have a function to handle it? I
found may ppl complaining about this via web-searching, but couldn't
find an answer I liked or an easy way to do it (i want to avoid having
to write code I would think has been done before).

Or, should I tell my customer that it is not good practice to have
their database server machine set in a way to observe changes in
daylight savings time?

Luch wrote:
It's perfectly correct to have the database set up to track daylight
savings, though the db itself needs to be set up accordingly (more
on this below,) - having host OS tracking daylight savings is not
sufficient. Ideally, you would transition from DATEs to TIMESTAMP
WITH TIME ZONEs, which save time zone information. But if all
you have is DATE and you're on 9i or later, you can

SELECT SYS_EXTRACT_UTC
(
FROM_TZ(
CAST(SYSDATE AS TIMESTAMP)
,DBTIMEZONE
)
) FROM DUAL;

(substitute SYSDATE with your DATE columns/variables.)

To make sure the db tracks daylight savings, you also need to

ALTER DATABASE SET TIME_ZONE='US/Pacific';

so that Oracle knows when daylight/standard transitions are taking
place and in which time zone the db is. Note that this command
may fail if your database has tables with TIMESTAMP WITH LOCAL
TIME ZONE columns (it takes only one such table and column
for db alteration to fail.)

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com