to_date in oracle 9i with time zone
harris
zone" information. I am getting date/time information from a text
file, which I want to insert into database table.
The date and time information includes time zone information which
needs to be stored into database time.
I tried this -
SQL>select to_date('26-OCT-2003 01:00:00 PST', 'DD-MON-YYYY HH24:MI:SS
TZD') from dual;
It shows me error -
ORA-01821: date format not recognized
SQL>select to_date('26-OCT-2003 01:00:00 -07:00', 'DD-MON-YYYY
HH24:MI:SS TZH:TZM') from dual;
shows same-
ORA-01821: date format not recognized
Question-
Anyone knows how to save "time zone" information into the "date" type
column?
Thank you in advance.
Harris M.
Vladimir M. Zakharychev
news:af968c64.03060...@posting.google.com...
> I am using Oracle 9i. Oracle documentation says 9i supports "time
> zone" information. I am getting date/time information from a text
> file, which I want to insert into database table.
>
>
> Anyone knows how to save "time zone" information into the "date" type
> column?
>
I doubt it, as this is not possible. You need TIMESTAMP WITH TIME ZONE,
not DATE, you need to use TO_TIMESTAMP_TZ(), which will recognize
your format string, and you need to store these timestamps in a TIMESTAMP
WITH TIME ZONE column. DATEs are still unaware of time zones, and will
never be.
--
Vladimir Zakharychev (b...@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.
harris
information as follows-
SELECT TO_TIMESTAMP_TZ('26-OCT-2003, 02:00:00 PST', 'DD-MON-YYYY
HH24:MI:SS TZR') from dual;
works fine.
With daylight saving time zone it shows me error message-
SELECT TO_TIMESTAMP_TZ('26-OCT-2003, 02:00:00 PDT', 'DD-MON-YYYY
HH24:MI:SS TZR') from dual;
Shows me following-
ORA-01882: timezone region not found
So, timestamp does not understand "daylight saving" time zone.
Harris M.
"Vladimir M. Zakharychev" <b...@dpsp-yes.com> wrote in message news:<bc51o9$e94$1...@babylon.agtel.net>...
Sybrand Bakker
> Thank you Vladimir for the information. I tried to use time zone
> information as follows-
>
> SELECT TO_TIMESTAMP_TZ('26-OCT-2003, 02:00:00 PST', 'DD-MON-YYYY
> HH24:MI:SS TZR') from dual;
>
> works fine.
>
> With daylight saving time zone it shows me error message-
> SELECT TO_TIMESTAMP_TZ('26-OCT-2003, 02:00:00 PDT', 'DD-MON-YYYY
> HH24:MI:SS TZR') from dual;
>
> Shows me following-
> ORA-01882: timezone region not found
>
> So, timestamp does not understand "daylight saving" time zone.
>
> Harris M.
And you did verify that in the docs, before making ur bald statement?
--
Sybrand Bakker
Senior Oracle DBA
to reply remove '-verwijderdit' from my e-mail address
harris
know or can not find information.
I am looking for answer to very specific problem. I did looked into
documentation from Oracle before posting question here.
Check this page-
http://www.csis.gvsu.edu/GeneralInfo/Oracle/server.920/a96540/sql_elements4a.htm#48515
Search for TZD or PDT
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.
Harris M.
"Sybrand Bakker" <pos...@sybrandb.demon.nl> wrote in message news:<vehusem...@corp.supernews.com>...
Vladimir M. Zakharychev
> 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.)