Hi team,
I have a problem with adding/subtracting Interval datatype to Timestamp datatype, when precision of the Interval is between 7 and 9.
It seems that it doesn't work correctly, see results 'EQ' (equals=wrong) in below example.
The second query is showing that PL/SQL works well in this aspect.
Test case:
select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
select
to_char(to_timestamp('1.1.2017 00:00:00.000000001','dd.mm.yyyy hh24:mi:ss.ff')+interval '0.000001' second,'dd.mm.yyyy hh24:mi:ss.ff') as val_p6,
case when to_timestamp('1.1.2017 00:00:00.000000001','dd.mm.yyyy hh24:mi:ss.ff')+interval '0.000001' second = to_timestamp('1.1.2017 00:00:00.000000001','dd.mm.yyyy hh24:mi:ss.ff') then 'EQ' else 'NE' end as equals_p6,
to_char(to_timestamp('1.1.2017 00:00:00.000000001','dd.mm.yyyy hh24:mi:ss.ff')+interval '0.0000001' second,'dd.mm.yyyy hh24:mi:ss.ff') as val_p7,
case when to_timestamp('1.1.2017 00:00:00.000000001','dd.mm.yyyy hh24:mi:ss.ff')+interval '0.0000001' second = to_timestamp('1.1.2017 00:00:00.000000001','dd.mm.yyyy hh24:mi:ss.ff') then 'EQ' else 'NE' end as equals_p7,
to_char(to_timestamp('1.1.2017 00:00:00.000000001','dd.mm.yyyy hh24:mi:ss.ff')+interval '0.000000001' second,'dd.mm.yyyy hh24:mi:ss.ff') as val_p9,
case when to_timestamp('1.1.2017 00:00:00.000000001','dd.mm.yyyy hh24:mi:ss.ff')+interval '0.000000001' second = to_timestamp('1.1.2017 00:00:00.000000001','dd.mm.yyyy hh24:mi:ss.ff') then 'EQ' else 'NE' end as equals_p9
from dual;
VAL_P6 EQ VAL_P7 EQ VAL_P9 EQ
----------------------------- -- ----------------------------- -- ----------------------------- --
01.01.2017 00:00:00.000001001 NE 01.01.2017 00:00:00.000000001 EQ 01.01.2017 00:00:00.000000001 EQ
set serveroutput on
declare
v_sql varchar2(2);
v_plsql varchar2(2);
begin
v_plsql := case when to_timestamp('1.1.2017 00:00:00.000000001','dd.mm.yyyy hh24:mi:ss.ff')+interval '0.000000001' second = to_timestamp('1.1.2017 00:00:00.000000001','dd.mm.yyyy hh24:mi:ss.ff') then 'EQ' else 'NE' end;
execute immediate q'[select case when to_timestamp('1.1.2017 00:00:00.000000001','dd.mm.yyyy hh24:mi:ss.ff')+interval '0.000000001' second = to_timestamp('1.1.2017 00:00:00.000000001','dd.mm.yyyy hh24:mi:ss.ff') then 'EQ' else 'NE' end from dual]' into v_sql;
dbms_output.put_line('v_sql='||to_char(v_sql)||', v_plsql='||to_char(v_plsql));
end;
/
PL/SQL procedure successfully completed.
v_sql=EQ, v_plsql=NE
Could you, please, give me a feedback concerning this issue?
It is a default precision issue. If your platform has a default precision of 6, then you get 6 when using just a simple interval command. Hence 7 digits gets rounded down to 6.
Examples:
SQL> select
2 to_char(to_timestamp('1.1.2017 00:00:00.000000001','dd.mm.yyyy hh24:mi:ss.ff')+
3 interval '0.000000001' second,'dd.mm.yyyy hh24:mi:ss.ff') as c1
4 from dual;
C1
-----------------------------
01.01.2017 00:00:00.000000001 <== looks wrong
SQL>
SQL> select
2 to_char(to_timestamp('1.1.2017 00:00:00.000000001','dd.mm.yyyy hh24:mi:ss.ff')+
3 interval '0.000000001' second(2,9),'dd.mm.yyyy hh24:mi:ss.ff') as c1
4 from dual;
C1
-----------------------------
01.01.2017 00:00:00.000000002 <==== correct