Recently we scheduled a job to monitor the orders process status. The statement looks like,
SELECT *
FROM ORDERS
WHERE STATUS_LAST_UPDT_TS
BETWEEN TRUNC (SYSTIMESTAMP) AND SYSTIMESTAMP - 30 / 1440
The job is scheduled at 3PM everyday so it should get the orders which were last updated between midnight and 2:30PM. However we found orders placed at 2:30 to 3:00 PM were returned as well. After some investigations I found out the root cause is the data type of systimestamp.
First, let us go over some basic knowledge of timestamp.
Oracle timestamp is an extension of the date datatype. It can store date and time data, including fractional seconds.
Timestamp with time zone is an extension of the timestamp datatype.It includes the time zone information.
Internally the timestamp type takes 11 bytes of storage and the timestamp with time zone takes 13 bytes.
Similar to sysdate, the function systimestamp returns the current timestamp with time zone of the database. localtimestamp returns the timestamp with the time zone of current session. For example,
alter session set time_zone = '-05:00';
Session altered.
select systimestamp,localtimestamp from dual;
SYSTIMESTAMP LOCALTIMESTAMP
-------------------------------------------------- --------------------------------------------------
27-DEC-12 1.47.42.257748 PM -06:00 27-DEC-12 2.47.42.257787 PM
Simiare to sysdate, we can do basic date arithmetic on systimestamp.
alter session set nls_date_format = 'DD-MON-YY HH.MI.SS AM';
Session altered.
select systimestamp, systimestamp - 30/1440 from dual;
SYSTIMESTAMP SYSTIMESTAMP-30/1440
-------------------------------------------------- --------------------------------------------------
27-DEC-12 1.53.48.752908 PM -06:00 27-DEC-12 1.23.48 PM
It works most of the time, but there is some problem with it. Unfortunately we run into this problem.
Here is our story. Our database is in Eastern Time Zone (-05:00) while we are in Central Time Zone (-06:00).
SQL> ALTER SESSION SET TIME_ZONE='-6:00';
Session altered.
SQL>
SELECT SYSTIMESTAMP
FROM DUAL
WHERE TO_TIMESTAMP_TZ('2012/12/27 14:25
-5:00','YYYY/MM/DD HH24:MI TZH:TZM')
BETWEEN TRUNC(SYSTIMESTAMP) AND
SYSTIMESTAMP - 30/1440;
SYSTIMESTAMP
---------------------------------------------------------------------------
27-DEC-12 02.33.18.399155 PM -05:00
1 row selected.
This
is not correct. The current time is 2:33 PM so 2:25 PM should not fall
between trunc(systimestamp) and timestamp - 30/1440.
SQL> ALTER SESSION SET TIME_ZONE='-5:00';
Session altered.
SQL> SELECT SYSTIMESTAMP
FROM DUAL
WHERE TO_TIMESTAMP_TZ('2012/12/27 14:25 -5:00','YYYY/MM/DD HH24:MI TZH:TZM')
BETWEEN TRUNC(SYSTIMESTAMP) AND SYSTIMESTAMP - 30/1440;
no rows selected
This is the right answer. But why does it happen?
The trick is. systimestamp is a timestamp with timezone but
(systimestamp - 30/1440) is not longer a timestamp, it is date type. It
can be easily demonstrated by,
SQL> select dump(systimestamp),dump(systimestamp-30/1440) from dual;
DUMP(SYSTIMESTAMP)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DUMP(SYSTIMESTAMP-30/1440)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Typ=188 Len=20: 7,220,12,27,19,39,0,0,59,94,163,144,251,0,5,0,0,0,0,0
Typ=13 Len=8: 7,220,12,27,14,9,0,0
