Re: new_time function with daylight savings

Luch wrote:
> 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?

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