RE: Selecting TZD, TZR for timezones
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <Adam.Donahue@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 10 Jan 2007 08:26:17 -0500
NLS_TIMESTAMP_TZ_FORMAT
08:02:37 PEGADMIN @ pegrept> select * from v$timezone_file;
FILENAME VERSION
------------ ----------
timezlrg.dat 2
1 row selected.
Elapsed: 00:00:00.09
08:06:24 PEGADMIN @ pegrept> create table t (dt date, ts1 timestamp
with time zone, ts2 timestamp with local time zone)
08:18:25 2 /
Table created.
Elapsed: 00:00:00.95
08:19:29 PEGADMIN @ pegrept> l
1 insert int t (dt, ts1, ts2) values
2 ( timestamp'2005-06-05 17:02:32.212 US/Pacific',
3 timestamp'2005-06-05 17:02:32.212 US/Pacific',
4* timestamp'2005-06-05 17:02:32.212 US/Pacific')
08:19:32 PEGADMIN @ pegrept> insert into t (dt, ts1, ts2) values
08:19:41 2 ( timestamp'2005-06-05 17:02:32.212 US/Pacific',
08:19:43 3 timestamp'2005-06-05 17:02:32.212 US/Pacific',
08:19:43 4 timestamp'2005-06-05 17:02:32.212 US/Pacific');
1 row created.
Elapsed: 00:00:00.03
08:19:45 PEGADMIN @ pegrept> commit;
Commit complete.
Elapsed: 00:00:00.01
08:19:50 PEGADMIN @ pegrept> select dbtimezone from dual;
DBTIME
------
-05:00
1 row selected.
Elapsed: 00:00:00.00
08:20:09 PEGADMIN @ pegrept> select * from t;
DT TS1
TS2
---------
------------------------------------------------------------------------
--- ---------------------------------------------
05-JUN-05 05-JUN-05 05.02.32.212000 PM US/PACIFIC
05-JUN-05 07.02.32.212000 PM
08:24:15 PEGADMIN @ pegrept> select * from t;
DT TS1
TS2
---------
------------------------------------------------------------------------
--- ------------------------------------
05-JUN-05 05-JUN-05 05.02.32.212000 PM -07
05-JUN-05 07.02.32.212000 PM
1 row selected.
Elapsed: 00:00:00.01
08:24:19 PEGADMIN @ pegrept> alter session set NLS_TIMESTAMP_TZ_FORMAT
= 'DD-MON-RR HH.MI.SSXFF AM TZM';
Session altered.
Elapsed: 00:00:00.01
08:24:48 PEGADMIN @ pegrept> select * from t;
DT TS1
TS2
---------
------------------------------------------------------------------------
--- ------------------------------------
05-JUN-05 05-JUN-05 05.02.32.212000 PM 00
05-JUN-05 07.02.32.212000 PM
1 row selected.
Elapsed: 00:00:00.01
08:24:51 PEGADMIN @ pegrept> alter session set NLS_TIMESTAMP_TZ_FORMAT
= 'DD-MON-RR HH.MI.SSXFF AM TZR'
08:24:59 2 ;
Session altered.
Elapsed: 00:00:00.01
08:25:00 PEGADMIN @ pegrept> select * from t;
DT TS1
TS2
---------
------------------------------------------------------------------------
--- ------------------------------------
05-JUN-05 05-JUN-05 05.02.32.212000 PM US/PACIFIC
05-JUN-05 07.02.32.212000 PM
Joel Patterson
Database Administrator
joel.patterson@xxxxxxxxxxx
x72546
904 727-2546
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Donahue, Adam
Sent: Tuesday, January 09, 2007 5:13 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Selecting TZD, TZR for timezones
Folks,
Is it possible to select the textual mapping of the current time zone
when the session (or database) time zone setting itself is numeric,
e.g.,
SQL> select systimestamp from dual;
SYSTIMESTAMP
------------------------------------------------------------------------
---
09-JAN-07 05.10.41.719683 PM -05:00
SQL> select to_char( systimestamp, 'TZR' ) from dual;
TO_CHAR(SYSTIMESTAMP,'TZR')
--------------------------------
-05:00
is not what we want. We want 'US/Eastern' returned from the second
prompt - the problem, I'm suspecting, is that there's no way to map from
-05:00 back to US/Eastern reliably without other details, and Oracle
doesn't have though details. Whereas I can supply a date and the time
zone in text which Oracle and convert to the right offset.
Is this possible?
Adam
--
This message may contain confidential, proprietary, or legally
privileged information. No confidentiality or privilege is waived by any
transmission to an unintended recipient. If you are not an intended
recipient, please notify the sender and delete this message immediately.
Any views expressed in this message are those of the sender, not those
of any entity within the KBC Financial Products group of companies
(together referred to as "KBC FP").
This message does not create any obligation, contractual or otherwise,
on the part of KBC FP. It is not an offer (or solicitation of an offer)
of, or a recommendation to buy or sell, any financial product. Any
prices or other values included in this message are indicative only, and
do not necessarily represent current market prices, prices at which KBC
FP would enter into a transaction, or prices at which similar
transactions may be carried on KBC FP's own books. The information
contained in this message is provided "as is", without representations
or warranties, express or implied, of any kind. Past performance is not
indicative of future returns.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
