29)
When comparing TSLTZ and TSTZ output the result around or after DST times seams
wrong.
Often this mistake comes from the
result of previous point (or similar) or a misunderstanding of the TSLTZ
dataype. See the next examples that uses some time around 25/10/2009 02:20:00,
which is the DST transition period in Belgium. Europe/Brussels is UTC +02:00
during summer ( CEST ) and UTC +01:00 during winter ( CET ) .
-- you do some inserts based on
sysdate and systimestamp and and a Date rithmetic like:
-- insert into timetest values (sysdate +5 +30/48, systimestamp +5 +30/48, systimestamp +5 +30/48) ;
-- What actually happens is an insert of the DATE datatype which is
-- implicit casted to the column datatype using the SESSIONTIMEZONE.
alter session set time_zone = 'Europe/Brussels' ;
Drop table timetest;
create table timetest (datum date, tsltz timestamp with local time zone, tstz timestamp with timezone) ;
insert into timetest values (to_date('25/10/09 00:50:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 00:50:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 00:50:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values (to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values (to_date('25/10/09 01:50:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 01:50:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 01:50:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values (to_date('25/10/09 02:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 02:20:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 02:20:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values (to_date('25/10/09 02:50:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 02:50:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 02:50:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values (to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'));
commit;
-- now let's select the result of this with some formatting in the same timezone
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
select * from timetest order by TSLTZ ;
-- which gives
-- SQL> select * from timetest order by TSLTZ ;
-- DATUM
-- -------------------
-- TSLTZ
-- --------------------------------------------------------------------
-- TSTZ
-- --------------------------------------------------------------------
-- 25-10-2009 00:50:00
-- 25*10*2009 00:50:00
-- 25/10/2009 00:50:00 EUROPE/BRUSSELS CEST
-- 25-10-2009 01:20:00
-- 25*10*2009 01:20:00
-- 25/10/2009 01:20:00 EUROPE/BRUSSELS CEST
-- 25-10-2009 01:50:00
-- 25*10*2009 01:50:00
-- 25/10/2009 01:50:00 EUROPE/BRUSSELS CEST
-- 25-10-2009 02:20:00
-- 25*10*2009 02:20:00
-- 25/10/2009 02:20:00 EUROPE/BRUSSELS CET
-- 25-10-2009 02:50:00
-- 25*10*2009 02:50:00
-- 25/10/2009 02:50:00 EUROPE/BRUSSELS CET
-- 25-10-2009 03:20:00
-- 25*10*2009 03:20:00
-- 25/10/2009 03:20:00 EUROPE/BRUSSELS CET
--
-- 6 rows selected.
-- Note the absence of the "double time", this is simply seen a DATE has no
-- idea what timezone it is. hence the SESSION timezone is used when doing the
-- implicit casting to TSTZ or TSLTZ datatype.
-- But when doing the conversion Oracle has no idea if '02:20:00" is
-- CEST or CET, so it has to choose something, CET the second time it's 02:xx.
-- Note also the fundamental difference between TSLTZ - which does NOT store
-- the used timezone (implicit or explicit defined) , the date/time part
-- is converted to the database timezone using the CURRENT value for the
-- (session) timezone - and TSTZ which stores the actual timezone but
-- where no mapping to the session timeone is done when read.
-- now let's look at the UTC values:
alter session set time_zone = '+00:00' ;
select datum, tsltz , tstz AT TIME ZONE '+00:00' from timetest order by TSLTZ ;
-- DATUM
-- -------------------
-- TSLTZ
-- -------------------------------------------------------
-- TSTZATTIMEZONE'+00:00'
-- -------------------------------------------------------
-- 25-10-2009 00:50:00
-- 24*10*2009 22:50:00
-- 24/10/2009 22:50:00 +00:00
-- 25-10-2009 01:20:00
-- 24*10*2009 23:20:00
-- 24/10/2009 23:20:00 +00:00
-- 25-10-2009 01:50:00
-- 24*10*2009 23:50:00
-- 24/10/2009 23:50:00 +00:00
-- 25-10-2009 02:20:00
-- 25*10*2009 01:20:00
-- 25/10/2009 01:20:00 +00:00
-- 25-10-2009 02:50:00
-- 25*10*2009 01:50:00
-- 25/10/2009 01:50:00 +00:00
-- 25-10-2009 03:20:00
-- 25*10*2009 02:20:00
-- 25/10/2009 02:20:00 +00:00
--
-- 6 rows selected.
-- correct result
alter session set time_zone = '+02:00' ;
select datum, tsltz , tstz AT TIME ZONE '+02:00' from timetest order by TSLTZ ;
-- DATUM
-- -------------------
-- TSLTZ
-- -----------------------------------
-- TSTZATTIMEZONE'+02:00'
-- -----------------------------------
-- 25-10-2009 00:50:00
-- 25*10*2009 00:50:00
-- 25/10/2009 00:50:00 +02:00
-- 25-10-2009 01:20:00
-- 25*10*2009 01:20:00
-- 25/10/2009 01:20:00 +02:00
-- 25-10-2009 01:50:00
-- 25*10*2009 01:50:00
-- 25/10/2009 01:50:00 +02:00
-- 25-10-2009 02:20:00
-- 25*10*2009 03:20:00
-- 25/10/2009 03:20:00 +02:00
-- 25-10-2009 02:50:00
-- 25*10*2009 03:50:00
-- 25/10/2009 03:50:00 +02:00
-- 25-10-2009 03:20:00
-- 25*10*2009 04:20:00
-- 25/10/2009 04:20:00 +02:00
--
-- 6 rows selected.
-- If you do not use the "at timezone" for the tstz colum then you get
select datum, tsltz , tstz from timetest order by TSLTZ ;
-- DATUM
-- -------------------
-- TSLTZ
-- ------------------------------------------------------------------
-- TSTZ
-- ------------------------------------------------------------------
-- 25-10-2009 00:50:00
-- 25*10*2009 00:50:00
-- 25/10/2009 00:50:00 EUROPE/BRUSSELS CEST
-- 25-10-2009 01:20:00
-- 25*10*2009 01:20:00
-- 25/10/2009 01:20:00 EUROPE/BRUSSELS CEST
-- 25-10-2009 01:50:00
-- 25*10*2009 01:50:00
-- 25/10/2009 01:50:00 EUROPE/BRUSSELS CEST
-- 25-10-2009 02:20:00
-- 25*10*2009 03:20:00
-- 25/10/2009 02:20:00 EUROPE/BRUSSELS CET
-- 25-10-2009 02:50:00
-- 25*10*2009 03:50:00
-- 25/10/2009 02:50:00 EUROPE/BRUSSELS CET
-- 25-10-2009 03:20:00
-- 25*10*2009 04:20:00
-- 25/10/2009 03:20:00 EUROPE/BRUSSELS CET
--
-- 6 rows selected.
-- Which is again correct seen the TSLTZ colum is casted
-- to the session timezone (+02:00) but the TSTZ is NOT,
-- a TSTZ is reported in the stored timezone.
-- The TSLTZ behavior is even more clear when assuming next testcase:
-- which clearly illustrates the effect of the session timezone
-- on both insert as selct of a TSLTZ column.
Drop table timetest;
create table timetest (sessionTZ varchar2(100), datum date, tsltz timestamp with local time zone) ;
alter session set time_zone = 'Europe/Brussels';
insert into timetest values ( 'Europe/Brussels', to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values ('Europe/Brussels', to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'));
alter session set time_zone = '+01:00';
insert into timetest values ( '+01:00', to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values ('+01:00', to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'));
alter session set time_zone = '+02:00';
insert into timetest values ( '+02:00', to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values ('+02:00', to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'));
commit;
ALTER SESSION SET NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
set pages 100
SELECT DBTIMEZONE FROM DUAL;
alter session set time_zone = 'Europe/Brussels';
select * from timetest order by sessionTZ;
alter session set time_zone = '+01:00';
select * from timetest order by sessionTZ;
alter session set time_zone = '+02:00';
select * from timetest order by sessionTZ;
-- the output of this is:
SQL> SELECT DBTIMEZONE FROM DUAL;
DBTIME
------
+00:00
SQL> alter session set time_zone = 'Europe/Brussels';
Session altered.
SQL> select * from timetest order by sessionTZ;
SESSIONTZ
-----------------------------------------------------------
DATUM
-------------------
TSLTZ
-----------------------------------------------------------
+01:00
25-10-2009 01:20:00
25*10*2009 02:20:00
+01:00
25-10-2009 03:20:00
25*10*2009 03:20:00
+02:00
25-10-2009 03:20:00
25*10*2009 02:20:00
+02:00
25-10-2009 01:20:00
25*10*2009 01:20:00
Europe/Brussels
25-10-2009 03:20:00
25*10*2009 03:20:00
Europe/Brussels
25-10-2009 01:20:00
25*10*2009 01:20:00
6 rows selected.
SQL> alter session set time_zone = '+01:00';
Session altered.
SQL> select * from timetest order by sessionTZ;
SESSIONTZ
-----------------------------------------------------------
DATUM
-------------------
TSLTZ
-----------------------------------------------------------
+01:00
25-10-2009 01:20:00
25*10*2009 01:20:00
+01:00
25-10-2009 03:20:00
25*10*2009 03:20:00
+02:00
25-10-2009 03:20:00
25*10*2009 02:20:00
+02:00
25-10-2009 01:20:00
25*10*2009 00:20:00
Europe/Brussels
25-10-2009 03:20:00
25*10*2009 03:20:00
Europe/Brussels
25-10-2009 01:20:00
25*10*2009 00:20:00
6 rows selected.
SQL> alter session set time_zone = '+02:00';
Session altered.
SQL> select * from timetest order by sessionTZ;
SESSIONTZ
-----------------------------------------------------------
DATUM
-------------------
TSLTZ
-----------------------------------------------------------
+01:00
25-10-2009 01:20:00
25*10*2009 02:20:00
+01:00
25-10-2009 03:20:00
25*10*2009 04:20:00
+02:00
25-10-2009 03:20:00
25*10*2009 03:20:00
+02:00
25-10-2009 01:20:00
25*10*2009 01:20:00
Europe/Brussels
25-10-2009 03:20:00
25*10*2009 04:20:00
Europe/Brussels
25-10-2009 01:20:00
25*10*2009 01:20:00
6 rows selected.
-- insert into timetest values (sysdate +5 +30/48, systimestamp +5 +30/48, systimestamp +5 +30/48) ;
-- What actually happens is an insert of the DATE datatype which is
-- implicit casted to the column datatype using the SESSIONTIMEZONE.
alter session set time_zone = 'Europe/Brussels' ;
Drop table timetest;
create table timetest (datum date, tsltz timestamp with local time zone, tstz timestamp with timezone) ;
insert into timetest values (to_date('25/10/09 00:50:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 00:50:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 00:50:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values (to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values (to_date('25/10/09 01:50:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 01:50:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 01:50:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values (to_date('25/10/09 02:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 02:20:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 02:20:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values (to_date('25/10/09 02:50:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 02:50:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 02:50:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values (to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'));
commit;
-- now let's select the result of this with some formatting in the same timezone
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
select * from timetest order by TSLTZ ;
-- which gives
-- SQL> select * from timetest order by TSLTZ ;
-- DATUM
-- -------------------
-- TSLTZ
-- --------------------------------------------------------------------
-- TSTZ
-- --------------------------------------------------------------------
-- 25-10-2009 00:50:00
-- 25*10*2009 00:50:00
-- 25/10/2009 00:50:00 EUROPE/BRUSSELS CEST
-- 25-10-2009 01:20:00
-- 25*10*2009 01:20:00
-- 25/10/2009 01:20:00 EUROPE/BRUSSELS CEST
-- 25-10-2009 01:50:00
-- 25*10*2009 01:50:00
-- 25/10/2009 01:50:00 EUROPE/BRUSSELS CEST
-- 25-10-2009 02:20:00
-- 25*10*2009 02:20:00
-- 25/10/2009 02:20:00 EUROPE/BRUSSELS CET
-- 25-10-2009 02:50:00
-- 25*10*2009 02:50:00
-- 25/10/2009 02:50:00 EUROPE/BRUSSELS CET
-- 25-10-2009 03:20:00
-- 25*10*2009 03:20:00
-- 25/10/2009 03:20:00 EUROPE/BRUSSELS CET
--
-- 6 rows selected.
-- Note the absence of the "double time", this is simply seen a DATE has no
-- idea what timezone it is. hence the SESSION timezone is used when doing the
-- implicit casting to TSTZ or TSLTZ datatype.
-- But when doing the conversion Oracle has no idea if '02:20:00" is
-- CEST or CET, so it has to choose something, CET the second time it's 02:xx.
-- Note also the fundamental difference between TSLTZ - which does NOT store
-- the used timezone (implicit or explicit defined) , the date/time part
-- is converted to the database timezone using the CURRENT value for the
-- (session) timezone - and TSTZ which stores the actual timezone but
-- where no mapping to the session timeone is done when read.
-- now let's look at the UTC values:
alter session set time_zone = '+00:00' ;
select datum, tsltz , tstz AT TIME ZONE '+00:00' from timetest order by TSLTZ ;
-- DATUM
-- -------------------
-- TSLTZ
-- -------------------------------------------------------
-- TSTZATTIMEZONE'+00:00'
-- -------------------------------------------------------
-- 25-10-2009 00:50:00
-- 24*10*2009 22:50:00
-- 24/10/2009 22:50:00 +00:00
-- 25-10-2009 01:20:00
-- 24*10*2009 23:20:00
-- 24/10/2009 23:20:00 +00:00
-- 25-10-2009 01:50:00
-- 24*10*2009 23:50:00
-- 24/10/2009 23:50:00 +00:00
-- 25-10-2009 02:20:00
-- 25*10*2009 01:20:00
-- 25/10/2009 01:20:00 +00:00
-- 25-10-2009 02:50:00
-- 25*10*2009 01:50:00
-- 25/10/2009 01:50:00 +00:00
-- 25-10-2009 03:20:00
-- 25*10*2009 02:20:00
-- 25/10/2009 02:20:00 +00:00
--
-- 6 rows selected.
-- correct result
alter session set time_zone = '+02:00' ;
select datum, tsltz , tstz AT TIME ZONE '+02:00' from timetest order by TSLTZ ;
-- DATUM
-- -------------------
-- TSLTZ
-- -----------------------------------
-- TSTZATTIMEZONE'+02:00'
-- -----------------------------------
-- 25-10-2009 00:50:00
-- 25*10*2009 00:50:00
-- 25/10/2009 00:50:00 +02:00
-- 25-10-2009 01:20:00
-- 25*10*2009 01:20:00
-- 25/10/2009 01:20:00 +02:00
-- 25-10-2009 01:50:00
-- 25*10*2009 01:50:00
-- 25/10/2009 01:50:00 +02:00
-- 25-10-2009 02:20:00
-- 25*10*2009 03:20:00
-- 25/10/2009 03:20:00 +02:00
-- 25-10-2009 02:50:00
-- 25*10*2009 03:50:00
-- 25/10/2009 03:50:00 +02:00
-- 25-10-2009 03:20:00
-- 25*10*2009 04:20:00
-- 25/10/2009 04:20:00 +02:00
--
-- 6 rows selected.
-- If you do not use the "at timezone" for the tstz colum then you get
select datum, tsltz , tstz from timetest order by TSLTZ ;
-- DATUM
-- -------------------
-- TSLTZ
-- ------------------------------------------------------------------
-- TSTZ
-- ------------------------------------------------------------------
-- 25-10-2009 00:50:00
-- 25*10*2009 00:50:00
-- 25/10/2009 00:50:00 EUROPE/BRUSSELS CEST
-- 25-10-2009 01:20:00
-- 25*10*2009 01:20:00
-- 25/10/2009 01:20:00 EUROPE/BRUSSELS CEST
-- 25-10-2009 01:50:00
-- 25*10*2009 01:50:00
-- 25/10/2009 01:50:00 EUROPE/BRUSSELS CEST
-- 25-10-2009 02:20:00
-- 25*10*2009 03:20:00
-- 25/10/2009 02:20:00 EUROPE/BRUSSELS CET
-- 25-10-2009 02:50:00
-- 25*10*2009 03:50:00
-- 25/10/2009 02:50:00 EUROPE/BRUSSELS CET
-- 25-10-2009 03:20:00
-- 25*10*2009 04:20:00
-- 25/10/2009 03:20:00 EUROPE/BRUSSELS CET
--
-- 6 rows selected.
-- Which is again correct seen the TSLTZ colum is casted
-- to the session timezone (+02:00) but the TSTZ is NOT,
-- a TSTZ is reported in the stored timezone.
-- The TSLTZ behavior is even more clear when assuming next testcase:
-- which clearly illustrates the effect of the session timezone
-- on both insert as selct of a TSLTZ column.
Drop table timetest;
create table timetest (sessionTZ varchar2(100), datum date, tsltz timestamp with local time zone) ;
alter session set time_zone = 'Europe/Brussels';
insert into timetest values ( 'Europe/Brussels', to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values ('Europe/Brussels', to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'));
alter session set time_zone = '+01:00';
insert into timetest values ( '+01:00', to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values ('+01:00', to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'));
alter session set time_zone = '+02:00';
insert into timetest values ( '+02:00', to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values ('+02:00', to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'));
commit;
ALTER SESSION SET NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
set pages 100
SELECT DBTIMEZONE FROM DUAL;
alter session set time_zone = 'Europe/Brussels';
select * from timetest order by sessionTZ;
alter session set time_zone = '+01:00';
select * from timetest order by sessionTZ;
alter session set time_zone = '+02:00';
select * from timetest order by sessionTZ;
-- the output of this is:
SQL> SELECT DBTIMEZONE FROM DUAL;
DBTIME
------
+00:00
SQL> alter session set time_zone = 'Europe/Brussels';
Session altered.
SQL> select * from timetest order by sessionTZ;
SESSIONTZ
-----------------------------------------------------------
DATUM
-------------------
TSLTZ
-----------------------------------------------------------
+01:00
25-10-2009 01:20:00
25*10*2009 02:20:00
+01:00
25-10-2009 03:20:00
25*10*2009 03:20:00
+02:00
25-10-2009 03:20:00
25*10*2009 02:20:00
+02:00
25-10-2009 01:20:00
25*10*2009 01:20:00
Europe/Brussels
25-10-2009 03:20:00
25*10*2009 03:20:00
Europe/Brussels
25-10-2009 01:20:00
25*10*2009 01:20:00
6 rows selected.
SQL> alter session set time_zone = '+01:00';
Session altered.
SQL> select * from timetest order by sessionTZ;
SESSIONTZ
-----------------------------------------------------------
DATUM
-------------------
TSLTZ
-----------------------------------------------------------
+01:00
25-10-2009 01:20:00
25*10*2009 01:20:00
+01:00
25-10-2009 03:20:00
25*10*2009 03:20:00
+02:00
25-10-2009 03:20:00
25*10*2009 02:20:00
+02:00
25-10-2009 01:20:00
25*10*2009 00:20:00
Europe/Brussels
25-10-2009 03:20:00
25*10*2009 03:20:00
Europe/Brussels
25-10-2009 01:20:00
25*10*2009 00:20:00
6 rows selected.
SQL> alter session set time_zone = '+02:00';
Session altered.
SQL> select * from timetest order by sessionTZ;
SESSIONTZ
-----------------------------------------------------------
DATUM
-------------------
TSLTZ
-----------------------------------------------------------
+01:00
25-10-2009 01:20:00
25*10*2009 02:20:00
+01:00
25-10-2009 03:20:00
25*10*2009 04:20:00
+02:00
25-10-2009 03:20:00
25*10*2009 03:20:00
+02:00
25-10-2009 01:20:00
25*10*2009 01:20:00
Europe/Brussels
25-10-2009 03:20:00
25*10*2009 04:20:00
Europe/Brussels
25-10-2009 01:20:00
25*10*2009 01:20:00
6 rows selected.
Thit is possible and intended. A timezone offset cannot be
something like -04:-30 but the timezone minutes "alone" can be
negative , -4 hours +30 minutes wouldn't make sense.
There are not many timezones who are affected, most known is the Venezuela America/Caracas timezone.
There are not many timezones who are affected, most known is the Venezuela America/Caracas timezone.
drop table test;
create table test (col1 timestamp with time zone);
insert into test values (to_timestamp_TZ('21/12/2010 US/Pacific','DD/MM/YYYY TZR'));
insert into test values (to_timestamp_TZ('21/12/2010 America/Caracas','DD/MM/YYYY TZR'));
insert into test values (to_timestamp_TZ('21/12/2010 Europe/Brussels','DD/MM/YYYY TZR'));
commit;
SELECT TZ_OFFSET(to_char(col1,'TZR')) from test;
SELECT extract(TIMEZONE_HOUR from col1) from test;
SELECT extract(TIMEZONE_MINUTE from col1) from test;
-- gives:
SQL> SELECT TZ_OFFSET(to_char(col1,'TZR')) from test;
TZ_OFFS
-------
-08:00
-04:30
+01:00
SQL> SELECT extract(TIMEZONE_HOUR from col1) from test;
EXTRACT(TIMEZONE_HOURFROMCOL1)
------------------------------
-8
-4
1
SQL> SELECT extract(TIMEZONE_MINUTE from col1) from test;
EXTRACT(TIMEZONE_MINUTEFROMCOL1)
--------------------------------
0
-30
0
create table test (col1 timestamp with time zone);
insert into test values (to_timestamp_TZ('21/12/2010 US/Pacific','DD/MM/YYYY TZR'));
insert into test values (to_timestamp_TZ('21/12/2010 America/Caracas','DD/MM/YYYY TZR'));
insert into test values (to_timestamp_TZ('21/12/2010 Europe/Brussels','DD/MM/YYYY TZR'));
commit;
SELECT TZ_OFFSET(to_char(col1,'TZR')) from test;
SELECT extract(TIMEZONE_HOUR from col1) from test;
SELECT extract(TIMEZONE_MINUTE from col1) from test;
-- gives:
SQL> SELECT TZ_OFFSET(to_char(col1,'TZR')) from test;
TZ_OFFS
-------
-08:00
-04:30
+01:00
SQL> SELECT extract(TIMEZONE_HOUR from col1) from test;
EXTRACT(TIMEZONE_HOURFROMCOL1)
------------------------------
-8
-4
1
SQL> SELECT extract(TIMEZONE_MINUTE from col1) from test;
EXTRACT(TIMEZONE_MINUTEFROMCOL1)
--------------------------------
0
-30
0
31) How to cast a DATE or TIMESTAMP to another timezone?
note: do not use the NEW_TIME()
function, this does not handle DST see note 419404.1 DST Has No
Effect on NEW_TIME function
There are several way's but
this is the most intuitive.
It's important to realise
that the "DATE" and "TIMESTAMP" datatype has no timezone
attributed . It may have a implicit timezone
, because you know the
timezone of the server, but the datatype itself is not aware of this.
If you want to use timezone
aware timestamps then the best solution is to not use "sysdate" and
the DATE or TIMESTAMP
datatype but Timestamp with
timezone and store the time / date with a full timezone , for example from
systimestamp.
If you want to
"convert" the datetime information in a DATE or TIMESTAMP datatype to
an other timezone you first of all need to
be very sure what timezone
this DATE or TIMESTAMP is actually supposed to be.
For example the "US
Central Time" might have different meanings, you have Central Standard
Time (CST) = GMT-6 but a in a
lof of places DST is applied
( Central Daylight Time (CDT) = GMT-5 ).
So you need to use the
correct Olson/oracle timezone, for example for an "US central
location" that has DST use
America/Chicago.
Make also sure your rdbms
DST version is up to date for the timezones used , see note
412160.1 Updated DST transitions and
new Time Zones in Oracle
Time Zone File patches
You can then construct from
a date or timestamp a timstamp with timezone with the correct timezone , cast
that at a different
timezone and (if needed)
back to a date or timestamp.
Here an example of casting a
date that is know to be actually "US Central Time" (= with DST) to
the Chinese timezone.
-- to make things clear in the
output
ALTER SESSION SET
NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET
NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT
='DD-MM-YYYY HH24:MI:SS';
drop table test;
create table test (col date);
insert into test values (
to_date('25/11/2011 12:20:00','DD-MM-YYYY HH24:MI:SS'));
insert into test values (
to_date('25/06/2011 12:20:00','DD-MM-YYYY HH24:MI:SS'));
Commit;
select * from test;
-- to_timestamp_TZ uses a string
not a date as input so use a explicit date format mask
select
to_timestamp_TZ(to_char(col,'DD-MM-YYYY HH24:MI:SS') ||'
America/Chicago','DD-MM-YYYY
HH24:MI:SS TZR') from test;
-- then you can use the at time
zone operator
select
to_timestamp_TZ(to_char(col,'DD-MM-YYYY HH24:MI:SS') ||'
America/Chicago','DD-MM-YYYY
HH24:MI:SS TZR') AT TIME ZONE
'Asia/Chongqing' from test;
-- then, if needed you can cast it
back to a date
-- again use explicit to_char
first
select
to_date(to_char(to_timestamp_TZ(to_char(col,'DD-MM-YYYY HH24:MI:SS') ||'
America/Chicago','DD-MM-YYYY
HH24:MI:SS TZR') AT TIME ZONE 'Asia/Chongqing','DD-MM-YYYY
HH24:MI:SS'),'DD-MM-YYYY
HH24:MI:SS') from test;
SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT
='DD/MM/YYYY HH24:MI:SS TZR TZD';
Session altered.
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT
='DD*MM*YYYY HH24:MI:SS';
Session altered.
SQL> ALTER SESSION SET NLS_DATE_FORMAT
='DD-MM-YYYY HH24:MI:SS';
Session altered.
SQL> drop table test;
Table dropped.
SQL> create table test (col date);
Table created.
SQL> insert into test values (
to_date('25/11/2011 12:20:00','DD-MM-YYYY HH24:MI:SS'));
1 row created.
SQL> insert into test values (
to_date('25/06/2011 12:20:00','DD-MM-YYYY HH24:MI:SS'));
1 row created.
SQL> Commit;
Commit complete.
SQL> select * from test;
COL
-------------------
25-11-2011 12:20:00
25-06-2011 12:20:00
SQL> -- to_timestamp_TZ uses a string not a
date as input so use a explicit date format mask
SQL> select to_timestamp_TZ(to_char(col,'DD-MM-YYYY
HH24:MI:SS') ||' America/Chicago','DD-MM-YYYY
HH24:MI:SS TZR') from test;
TO_TIMESTAMP_TZ(TO_CHAR(COL,'DD-MM-YYYYHH24:MI:SS')||'AMERICA/CHICAGO','DD-
---------------------------------------------------------------------------
25/11/2011 12:20:00 AMERICA/CHICAGO CST
25/06/2011 12:20:00 AMERICA/CHICAGO CDT
SQL> -- then you can use the at time zone
operator
SQL> select
to_timestamp_TZ(to_char(col,'DD-MM-YYYY HH24:MI:SS') ||'
America/Chicago','DD-MM-YYYY
HH24:MI:SS TZR') AT TIME ZONE 'Asia/Chongqing'
from test;
TO_TIMESTAMP_TZ(TO_CHAR(COL,'DD-MM-YYYYHH24:MI:SS')||'AMERICA/CHICAGO','DD-
---------------------------------------------------------------------------
26/11/2011 02:20:00 ASIA/CHONGQING CST
26/06/2011 01:20:00 ASIA/CHONGQING CST
SQL> -- then, if needed you can cast it
back to a date
SQL> -- again use explicit to_char first
SQL> select
to_date(to_char(to_timestamp_TZ(to_char(col,'DD-MM-YYYY HH24:MI:SS')||'
America/Chicago','DD-MM-YYYY HH24:MI:SS TZR')
AT TIME ZONE 'Asia/Chongqing','DD-MM-YYYY
HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') from
test;
TO_DATE(TO_CHAR(TO_
-------------------
26-11-2011 02:20:00
26-06-2011 01:20:00
32) Should I worry when moving data from a database in one
timezone into a database in an other timezone using export/import or dblinks?
The "OS timezone"
has no impact on any stored value in DATE/TIMESTAMP/TSTZ/TSLTZ datatypes.
The DATE and TIMESTAMP
datatypes have no concept of timezone so that data will not change at all.
TimeStamp with Time Zone
(TSTZ) data (see point 1 in this note to see if you actually store user TSTZ
data) stores the time and the actual timezone offset/name used for that time at
the moment of insert. so you need to make sure that for BOTH databases the
RDBMS DST version is up to date for the used named timezones ( see note
412160.1 Updated DST transitions
and new
Time Zones in Oracle Time Zone File patches ). TSTZ data using offsets is not
affected by the RDBMS DST version.
So bottomline is, if you have user TSTZ
using named timezones, make sure your target database is using the same or a
higher RDBMS DST than the source.
TimeStamp with Local Time Zone (TSLTZ)
data (see point 1 in this note to see if you actually store TSLTZ data) stores
internally the time converted to/from the database timezone (see point 3) from
the timezone specified at insert/select time. The DBTIMEZONE should be an
OFFSET not a named timezone that has DST.
The actual DBTIMEZONE is however
transparent to the returned result. If your source database uses for example a DBTIMEZONE
set to -08:00 and the target database uses +07:00 then the internal values for
the stored TSLTZ will change (exp/imp and dblinks will adapt between them) but
if you select this column in both databases using the same SESSIONTIMEZONE the
result will be just the same.
33) How to get the time in UTC , GMT or epoch (Unix) time in
Oracle?
Technically there is a difference
between UTC and GMT, in Oracle RDBMS however UTC and GMT are considered the same.
This will give
"systimestamp in UTC" as a Timestamp with Timezone datatype result
regardless of any database or OS setting
ALTER SESSION SET
NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR ';
select systimestamp AT TIME ZONE
'UTC' from dual;
This will give
"systimestamp in UTC" as a Timestamp datatype regardless of any
database or OS setting
ALTER SESSION SET
NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS';
select
sys_extract_utc(systimestamp) from dual;
This will give a
"sysdate in UTC" as a date dataype regardless of any database or OS
setting
ALTER SESSION SET NLS_DATE_FORMAT
='YYYY-MM-DD HH24:MI:SS';
select
to_date(to_char(systimestamp AT TIME ZONE 'UTC','DD-MM-YYYY
HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') from dual;
-- or
select
to_date(to_char(sys_extract_utc(systimestamp),'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY
HH24:MI:SS')from dual;
Epoch time is by definition
in UTC, defined as the number of seconds that have elapsed since midnight
Coordinated Universal Time (UTC), 1 January 1970 .
Using "sysdate in
UTC" and subtract 01-01-1970 00:00:00 and multiply by 86400 to make a
epoch routine that is unaffected by the server timezone or database / session
settings.
select
to_number(to_date(to_char(systimestamp AT TIME ZONE 'UTC','DD-MM-YYYY
HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY
HH24:MI:SS')) * (86400) from dual;
-- or
select
to_number(to_date(to_char(sys_extract_utc(systimestamp),'DD-MM-YYYY
HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') - TO_DATE('01-01-1970 00:00:00',
'DD-MM-YYYY HH24:MI:SS')) * (86400) from dual;
No comments:
Post a Comment