Comparing date type column with a timestamp column

Question

I have a table say demo with three columns

create table demo(demo_id number,
                  created_date date,
                  mod_date systimestamp(6)
                 ); 

my requirement is to return a refcursor with two columns

1. Demo_id 
2. Status column 

Which will be derived as follows:

if created_date = mod_date then "New" else "Updated" . 

So I have written my code as :

select demo_id ,case when created_date=mod_date then "New" else "update" end from demo; 

but somehow even though the dates are same including the timecomponent My status value is always showing updated. I am using SQL developer to run my queries and have also modified the nls_date_format to be DD/mm/yyyy hh24:mi:SS. Also mod_date will be storing systimestamp.

Answer 1

You are comparing a date, which has a second precision, with a timestamp, which has fractional-second precision.

If created_date is taken from sysdate and mod_date is taken from systimestamp then you could be comparing something like 2022-08-06 23:36:58 with 2022-08-06 23:36:58.373657. Those are not the same.

The data type comparison and precedence rules mean that the date value is implicitly converted to a timestamp, but that just means it's now comparing 2022-08-06 23:36:58.000000 with 2022-08-06 23:36:58.373657. Those are still not the same.

As you can't add precision to the date, you will have to remove precision from the timestamp, which you can do by casting it to that data type:

case when created_date = cast(mod_date as date) then 'New' else 'Updated' end as status

db<>fiddle

However, if the row is modified during the same second it was created - e.g. from the example used before, if mod_date is set to 2022-08-06 23:36:58.999999 - then that won't be seen as an update, since once the precision is reduced the original and updated mod_date are identical, and both the same as created_date.

That might not be an issue for you, but it would still be simpler if you made created_date a timestamp as well, and set that with systimestamp too.