Milliseconds are lost after date to tz conversion on 19c

Hi,

yesterday, I found (maybe) a small bug in 19c. Whenever I deal with timestamp with time zones, I always learn something new. so, the request is as this:

there is a DATE column which should be converted into a timestamp with tz but if it is null then result should be SYSTIMESTAMP. it is pretty straight. here is the output in 19c (19.25 to be specific):

drop table test1;
create table test1 (d date);

insert into test1 values (sysdate);
insert into test1 values (null);
commit;

select d,
       nvl(from_tz(to_timestamp(d), '+02:00'), systimestamp ) new_data,
       systimestamp actual_systimestamp
from test1;

D                   NEW_DATA                             ACTUAL_SYSTIMESTAMP                 
------------------- ------------------------------------ ------------------------------------
2025-07-15 09:50:36 2025-07-15 09:50:36.000000000 +02:00 2025-07-15 09:52:59.666055000 +02:00
                    2025-07-15 09:53:00.000000000 +02:00 2025-07-15 09:52:59.666055000 +02:00

drop table test1;

create table test1 (d date);

insert into test1 values (sysdate);

insert into test1 values (null);

commit;

select d,

nvl(from_tz(to_timestamp(d), '+02:00'), systimestamp ) new_data,

systimestamp actual_systimestamp

from test1;

D NEW_DATA ACTUAL_SYSTIMESTAMP

------------------- ------------------------------------ ------------------------------------

2025-07-15 09:50:36 2025-07-15 09:50:36.000000000 +02:00 2025-07-15 09:52:59.666055000 +02:00

2025-07-15 09:53:00.000000000 +02:00 2025-07-15 09:52:59.666055000 +02:00

so the first row everything is correct. column D is not empty and data is converted into “timestamp with time zone” but for the second row, the with d is null, we see systimestmap output on second column (NEW_DATA) but if you compare it with actual systimestamp, you can see that MILLISECONDs are missing! they are all zero. Actually it is rounded up because actual systimestamp 09:52:59.666055 but after NVL function, result is 09:59:00.00000.

so what caused this? NVL, FROM_TZ or TO_TIMESTAMP?

first, Let’s replace NVL with COALESCE:

select d,
       coalesce(from_tz(to_timestamp(d), '+02:00'), systimestamp ) new_data,
       systimestamp actual_systimestamp
from test1;

D                   NEW_DATA                             ACTUAL_SYSTIMESTAMP                 
------------------- ------------------------------------ ------------------------------------
2025-07-15 09:56:51 2025-07-15 09:56:51.000000000 +02:00 2025-07-15 10:00:17.651128000 +02:00
                    2025-07-15 10:00:17.651128000 +02:00 2025-07-15 10:00:17.651128000 +02:00

select d,

coalesce(from_tz(to_timestamp(d), '+02:00'), systimestamp ) new_data,

systimestamp actual_systimestamp

from test1;

D NEW_DATA ACTUAL_SYSTIMESTAMP

------------------- ------------------------------------ ------------------------------------

2025-07-15 09:56:51 2025-07-15 09:56:51.000000000 +02:00 2025-07-15 10:00:17.651128000 +02:00

2025-07-15 10:00:17.651128000 +02:00 2025-07-15 10:00:17.651128000 +02:00

huh! this works. Now we have milliseconds for null date value. so problem is NVL ?

Let’s also add a “TIMESTAMP” column and remove TO_TIMESTAMP function as well (still using NVL):

drop table test1;
create table test1 (d date, t timestamp);

insert into test1 values (sysdate, localtimestamp);
insert into test1 values (null, null);
commit;

select d, t,
       nvl(from_tz(to_timestamp(d), '+02:00'), systimestamp ) new_data_date,
       nvl(from_tz(t, '+02:00'), systimestamp ) new_data_timestamp,
       systimestamp
from test1;

D                   T                             NEW_DATA_DATE                        NEW_DATA_TIMESTAMP                   SYSTIMESTAMP                        
------------------- ----------------------------- ------------------------------------ ------------------------------------ ------------------------------------
2025-07-15 09:56:51 2025-07-15 09:56:51.295299000 2025-07-15 09:56:51.000000000 +02:00 2025-07-15 09:56:51.295299000 +02:00 2025-07-15 10:01:52.339933000 +02:00
                                                  2025-07-15 10:01:52.000000000 +02:00 2025-07-15 10:01:52.339933000 +02:00 2025-07-15 10:01:52.339933000 +02:00

drop table test1;

create table test1 (d date, t timestamp);

insert into test1 values (sysdate, localtimestamp);

insert into test1 values (null, null);

commit;

select d, t,

nvl(from_tz(to_timestamp(d), '+02:00'), systimestamp ) new_data_date,

nvl(from_tz(t, '+02:00'), systimestamp ) new_data_timestamp,

systimestamp

from test1;

D T NEW_DATA_DATE NEW_DATA_TIMESTAMP SYSTIMESTAMP

------------------- ----------------------------- ------------------------------------ ------------------------------------ ------------------------------------

2025-07-15 09:56:51 2025-07-15 09:56:51.295299000 2025-07-15 09:56:51.000000000 +02:00 2025-07-15 09:56:51.295299000 +02:00 2025-07-15 10:01:52.339933000 +02:00

2025-07-15 10:01:52.000000000 +02:00 2025-07-15 10:01:52.339933000 +02:00 2025-07-15 10:01:52.339933000 +02:00

as you see, unlike date conversion, “nvl(from_tz(t, ‘+02:00’), systimestamp )” returns “correct” data! we can see milliseconds if we don’t need to_timestamp conversion. so is it to_timestamp? not clear.

but since NVL also causes the difference, this means return type of functions and actual data types are different:

select dump(t) dump_timestamp, dump(to_timestamp(d)) dump_to_timestamp
from test1
where t is not null;

DUMP_TIMESTAMP                                     DUMP_TO_TIMESTAMP
-------------------------------------------------  ------------------------------------------------------------
Typ=180 Len=11: 120,125,7,15,11,8,33,31,29,116,64  Typ=187 Len=20: 233,7,7,15,10,7,32,0,0,0,0,0,0,0,3,0,0,0,0,0

select dump(t) dump_timestamp, dump(to_timestamp(d)) dump_to_timestamp

from test1

where t is not null;

DUMP_TIMESTAMP DUMP_TO_TIMESTAMP

------------------------------------------------- ------------------------------------------------------------

Typ=180 Len=11: 120,125,7,15,11,8,33,31,29,116,64 Typ=187 Len=20: 233,7,7,15,10,7,32,0,0,0,0,0,0,0,3,0,0,0,0,0

so, the type of timestamp column in the table is 180 but to_timestamp function’s type is 187. there are many different examples of this kind of staff on dates/timestamps etc.

But why did I said that this could be a “bug” ? because it works as it is expected on 23ai:

select banner_full from v$version;
BANNER_FULL
--------------------------------
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems Version 23.7.0.25.01

drop table test1;
create table test1 (d date);

insert into test1 values (sysdate);
insert into test1 values (null);
commit;


select d,
       nvl(from_tz(to_timestamp(d), '+02:00'), systimestamp ) new_data,
       systimestamp actual_systimestamp
from test1;
D                   NEW_DATA                             ACTUAL_SYSTIMESTAMP                 
------------------- ------------------------------------ ------------------------------------
2025-07-15 10:15:50 2025-07-15 10:15:50.000000000 +02:00 2025-07-15 10:16:03.230645000 +02:00
                    2025-07-15 10:16:03.230645000 +02:00 2025-07-15 10:16:03.230645000 +02:00

select banner_full from v$version;

BANNER_FULL

--------------------------------

Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems Version 23.7.0.25.01

drop table test1;

create table test1 (d date);

insert into test1 values (sysdate);

insert into test1 values (null);

commit;

select d,

nvl(from_tz(to_timestamp(d), '+02:00'), systimestamp ) new_data,

systimestamp actual_systimestamp

from test1;

D NEW_DATA ACTUAL_SYSTIMESTAMP

------------------- ------------------------------------ ------------------------------------

2025-07-15 10:15:50 2025-07-15 10:15:50.000000000 +02:00 2025-07-15 10:16:03.230645000 +02:00

2025-07-15 10:16:03.230645000 +02:00 2025-07-15 10:16:03.230645000 +02:00

milliseconds are there. so I assume this was a bug in 19c.

wish you healthy, happy days.

Leave a Reply Cancel reply