Re: to_date in oracle 9i with time zone

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Sun, 15 Jun 2003 12:31:34 +0400
Message-ID: <bchau6$5tp$1@babylon.agtel.net>

> Besides this Oracle documentation talks about setting variables such
> as - NLS_DATE_FORMAT, which does not address my problem - which is
> given any date and time with "daylight saving" information, save and
> retrieve it with "daylight saving" information.
>
> I would appriciate if any answer to "Daylight saving into Oracle 9i".
> If Oracle 9i does not really support it as it says in advertizments,
> there are always "way around", which people have used in Oracle 8i.

You may want to check out this:

http://www.dynamicpsp.com/dpsp/prod/!go?ln=narticle&aid=1

To put it short, there are issues when zone name abbreviations, like PST/PDT, are used, because they are sometimes ambiguous. They can identify more than one zone, they changed over time (for example, there was once PWT zone - guess when and what it means), etc. Use region names, like 'US/Pacific', for identifying zones, and you will be fine - Oracle will take into account any daylight savings that may be in effect.

As of your experience with TO_TIMESTAMP_TZ(): there *is* PST region, but no PDT region, thus Oracle threw that ORA-01882 at you. However, you can't use TZD in TO_TIMESTAMP_TZ() because of inherent ambiguity of most time zone abbreviations - it's just ignored if it's there. Anyway, PST would always give you correct time:
SELECT TO_CHAR(
TO_TIMESTAMP_TZ('25-OCT-2003, 02:00:00 PST',

                               'DD-MON-YYYY HH24:MI:SS TZR'),

'DD-MON-YYYY HH24:MI:SS TZD') from dual;

25-OCT-2003 02:00:00 PDT SELECT TO_CHAR(
TO_TIMESTAMP_TZ('26-OCT-2003, 02:00:00 PST',

                              'DD-MON-YYYY HH24:MI:SS TZR'),

'DD-MON-YYYY HH24:MI:SS TZD') from dual;

26-OCT-2003 02:00:00 PST Btw, full list of region names and abbreviations supported by your version of Oracle can be obtained by querying the V$TIMEZONE_NAMES view (Oracle monitors public sources for timezone naming and continuously updates its internal maps, so your version may support more zones than documented.)

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.

Received on Sun Jun 15 2003 - 03:31:34 CDT