Introduction / Statement
If you use one of the following data types:
- TIMESTAMP WITH TIME ZONE
- TIMESTAMP WITH LOCAL TIME ZONE
And if you have a trigger that references to one of listed data type column
Then Oracle looses your milliseconds (rounds up to seconds to be precise)
It applies at least up to Oracle version 12.1.0.2
Impact
- There is silent/transparent data loss.
- In our case, java/hibernate optimistic locking does not work on "row last updated" column.
Example: Row insert has value T1 on java side with milliseconds. Next update goes to database and expects that "row last updated" value is T1 but it is not. In database there is rounded value of T1. As there is no match the optimistic locking makes conclusion that row is changed by other sessions and raises optimistic lock exception.
Test case
-- Create table with 3 different datatypes
-- 1) TIMESTAMP
-- 2) TIMESTAMP WITH LOCAL TIME ZONE
-- 3) TIMESTAMP WITH TIME ZONE
-- Create 2 columns for each datatype. One will be referenced by trigger
CREATE
TABLE
TEST
(
ID
NUMBER
,
TIME_1 TIMESTAMP
,
TIME_1_TRG TIMESTAMP
,
TIME_2 TIMESTAMP
WITH
LOCAL
TIME
ZONE
,
TIME_2_TRG TIMESTAMP
WITH
LOCAL
TIME
ZONE
,
TIME_3 TIMESTAMP
WITH
TIME
ZONE
,
TIME_3_TRG TIMESTAMP
WITH
TIME
ZONE
)
/
-- Create trigger.
-- Please not that trigger code never executes. "IF FALSE" can never be true.
-- it is enough that column is referenced in trigger to loose milliseconds
CREATE OR REPLACE TRIGGER TEST_BIU
-- it is enough that column is referenced in trigger to loose milliseconds
CREATE OR REPLACE TRIGGER TEST_BIU
BEFORE
INSERT
OR
UPDATE
ON
TEST
FOR
EACH
ROW
BEGIN
IF
FALSE
THEN
:new.TIME_1_TRG
:=
LOCALTIMESTAMP;
:new.TIME_2_TRG
:=
LOCALTIMESTAMP;
:new.TIME_3_TRG
:=
LOCALTIMESTAMP;
raise_application_error(-20001,
'this
line is added to show that this IF branch is not executed during this
test');
END
IF;
END;
/
-- Insert data into table.
/
-- Insert data into table.
-- There are 3 different values inserted to demonstrate that milliseconds are rounded (not truncated)
declare
declare
t1
TIMESTAMP
:=
TIMESTAMP
'2015-12-30
13:14:15.499';
t2
TIMESTAMP
:=
TIMESTAMP
'2015-12-30
13:14:15.500';
t3
TIMESTAMP
:=
TIMESTAMP
'2015-12-30
13:14:15.501';
begin
-- Insert value to demonstrate that INSERT is affected
INSERT INTO TEST VALUES ( 1, t1, t1, t1, t1, t1, t1);
INSERT INTO TEST VALUES ( 1, t1, t1, t1, t1, t1, t1);
-- Insert and Update value to demonstrate that UPDATE is affected as well
INSERT INTO TEST VALUES ( 2, t1, t1, t1, t1, t1, t1);
INSERT INTO TEST VALUES ( 2, t1, t1, t1, t1, t1, t1);
UPDATE
TEST
SET
TIME_1 =
t1,
TIME_1_TRG =
t1,
TIME_2 =
t1,
TIME_2_TRG =
t1,
TIME_3 =
t1,
TIME_3_TRG =
t1 WHERE
ID
=
2;
--
INSERT
INTO
TEST
VALUES
(
3,
t2,
t2,
t2,
t2,
t2,
t2);
INSERT
INTO
TEST
VALUES
(
4,
t2,
t2,
t2,
t2,
t2,
t2);
UPDATE
TEST
SET
TIME_1 =
t2,
TIME_1_TRG =
t2,
TIME_2 =
t2,
TIME_2_TRG =
t2,
TIME_3 =
t2,
TIME_3_TRG =
t2 WHERE
ID
=
4;
--
INSERT
INTO
TEST
VALUES
(
5,
t3,
t3,
t3,
t3,
t3,
t3);
INSERT
INTO
TEST
VALUES
(
6,
t3,
t3,
t3,
t3,
t3,
t3);
UPDATE
TEST
SET
TIME_1 =
t3,
TIME_1_TRG =
t3,
TIME_2 =
t3,
TIME_2_TRG =
t3,
TIME_3 =
t3,
TIME_3_TRG =
t3 WHERE
ID
=
6;
--
COMMIT;
end;
/
-- Display content of table
-- Display content of table
select
* from
test;
Result
This is result of test case.
Please note that red timestamp values are wrong (milliseconds are rounded) and blue values below are correct/expected.
| ID | TIME_1 | TIME_1_TRG | TIME_2 | TIME_2_TRG | TIME_3 | TIME_3_TRG |
| 1 | 30.12.2015 13:14:15,499 | 30.12.2015 13:14:15,499 | 30.12.2015 13:14:15,499 | 30.12.2015 13:14:15,000 | 30.12.2015 13:14:15,499 +02:00 | 30.12.2015 13:14:15,000 +02:00 |
| 2 | 30.12.2015 13:14:15,499 | 30.12.2015 13:14:15,499 | 30.12.2015 13:14:15,499 | 30.12.2015 13:14:15,000 | 30.12.2015 13:14:15,499 +02:00 | 30.12.2015 13:14:15,000 +02:00 |
| 3 | 30.12.2015 13:14:15,500 | 30.12.2015 13:14:15,500 | 30.12.2015 13:14:15,500 | 30.12.2015 13:14:16,000 | 30.12.2015 13:14:15,500 +02:00 | 30.12.2015 13:14:16,000 +02:00 |
| 4 | 30.12.2015 13:14:15,500 | 30.12.2015 13:14:15,500 | 30.12.2015 13:14:15,500 | 30.12.2015 13:14:16,000 | 30.12.2015 13:14:15,500 +02:00 | 30.12.2015 13:14:16,000 +02:00 |
| 5 | 30.12.2015 13:14:15,501 | 30.12.2015 13:14:15,501 | 30.12.2015 13:14:15,501 | 30.12.2015 13:14:16,000 | 30.12.2015 13:14:15,501 +02:00 | 30.12.2015 13:14:16,000 +02:00 |
| 6 | 30.12.2015 13:14:15,501 | 30.12.2015 13:14:15,501 | 30.12.2015 13:14:15,501 | 30.12.2015 13:14:16,000 | 30.12.2015 13:14:15,501 +02:00 | 30.12.2015 13:14:16,000 +02:00 |
Expected result
Here is expected result. Test case produces given result if trigger is not created.
| ID | TIME_1 | TIME_1_TRG | TIME_2 | TIME_2_TRG | TIME_3 | TIME_3_TRG |
| 1 | 30.12.2015 13:14:15,499 | 30.12.2015 13:14:15,499 | 30.12.2015 13:14:15,499 | 30.12.2015 13:14:15,499 | 30.12.2015 13:14:15,499 +02:00 | 30.12.2015 13:14:15,499 +02:00 |
| 2 | 30.12.2015 13:14:15,499 | 30.12.2015 13:14:15,499 | 30.12.2015 13:14:15,499 | 30.12.2015 13:14:15,499 | 30.12.2015 13:14:15,499 +02:00 | 30.12.2015 13:14:15,499 +02:00 |
| 3 | 30.12.2015 13:14:15,500 | 30.12.2015 13:14:15,500 | 30.12.2015 13:14:15,500 | 30.12.2015 13:14:15,500 | 30.12.2015 13:14:15,500 +02:00 | 30.12.2015 13:14:15,500 +02:00 |
| 4 | 30.12.2015 13:14:15,500 | 30.12.2015 13:14:15,500 | 30.12.2015 13:14:15,500 | 30.12.2015 13:14:15,500 | 30.12.2015 13:14:15,500 +02:00 | 30.12.2015 13:14:15,500 +02:00 |
| 5 | 30.12.2015 13:14:15,501 | 30.12.2015 13:14:15,501 | 30.12.2015 13:14:15,501 | 30.12.2015 13:14:15,501 | 30.12.2015 13:14:15,501 +02:00 | 30.12.2015 13:14:15,501 +02:00 |
| 6 | 30.12.2015 13:14:15,501 | 30.12.2015 13:14:15,501 | 30.12.2015 13:14:15,501 | 30.12.2015 13:14:15,501 | 30.12.2015 13:14:15,501 +02:00 | 30.12.2015 13:14:15,501 +02:00 |
References
Bug 21078160 - TIMESTAMP WITH LOCAL TIME ZONE COLUMN MILLISECONDS DISAPPEAR IF TRIGGER REFERENCE
Registered in May 2015
Bug 6878309 : MILLISECONDS OF TIMESTAMP IS LOST IN DATABASE TABLE TRIGGERS
Registered in March 2008
-- end --
