SYSTIMESTAMP INTERVAL Processing
12/12/2011 4 Comments
…or how to calculate dates in Oracle.
This is just a quick post to try to encourage the use of the INTERVAL function when adjusting (sys)timestamps (or dates). I thought this would be better expressed through a quick script with comments to show how using the traditional Oracle method of calculating fractions of a day can cause problems and make you have to think more than necessary.
There are 2 main issues with using the traditional Oracle method of calculating date/time changes. Firstly, it’s strange. You have to calculate fractions of a day. 1 second is 1/86400, 1 minute is 1/1440 [maybe 1/(24*60) expresses it better]. Secondly, with timestamps it causes an implicit conversion to a date type, with all of the unintended consequences that implicit conversion carries.
Lets run the script and see what happens:
Script Output
> -- Lets just set the date format explicitly before we start. > alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' session SET altered.
> -- Lets start simple and add a day. > select systimestamp, systimestamp + 1 from dual
SYSTIMESTAMP SYSTIMESTAMP+1 ------------- ------------------------- 09-DEC-11 18.39.53.026000000 +00:00 10-DEC-2011 18:39:53
> -- Hang on, doing this the traditional Oracle way has implicitly cast > -- the timestamp into a sysdate format. We need to convert it back. > -- Lets try that again, but add 3 hours instead > select systimestamp, to_timestamp( > systimestamp + (3/24), > 'DD-MON-YYYY HH24:MI:SS' ) from dual
SYSTIMESTAMP TO_TIMESTAMP(SYSTIMESTAMP+(3/24),'DD-MON-YYYYHH24:MI:SS') ------------- ------------------------- 09-DEC-11 18.39.53.026000000 +00:00 09-DEC-11 21.39.53.000000000
> -- So we have an implicit conversion, and we've lost the timestamp precision. > -- Now lets add 3 hours, 5 minutes 10.5 seconds. >select systimestamp, to_timestamp( > systimestamp + (3/24) + (5/(24*60)) + (10.5/24/60/60), > 'DD-MON-YYYY HH24:MI:SS') from dual
SYSTIMESTAMP TO_TIMESTAMP(SYSTIMESTAMP+(3/24)+(5/(24*60))+(10.5/24/60/60),'DD-MON-YYYYHH24:MI:SS') ------------- ------------------------- 09-DEC-11 18.39.53.026000000 +00:00 09-DEC-11 21.45.04.000000000
> -- And we completely lost the .5 in the 10.5 as we've converted to a sysdate. > -- It was rounded up. You might also have noticed that I have used some > -- different date processing to calculate fractions of a day. > -- There are many different way to calculate time in Oracle: > -- 10 minutes can be 10/1440, 10/60/24, 10/(60*24), 0.00694, 600/86400 etc, > -- It's all tricky to understand and standards vary from company to company, > -- if the company actually has a standard. > -- There is a better way, using the INTERVAL function. It goes like this: > -- Lets start simple and add a day.
> select systimestamp, systimestamp + INTERVAL '1' day from dual
SYSTIMESTAMP SYSTIMESTAMP+INTERVAL'1'DAY ------------- --------------------------- 09-DEC-11 18.39.53.042000000 +00:00 10-DEC-11 18.39.53.042000000 +00:00
> -- So far so good. And we haven't lost the data type either! > -- No implicit conversion to break our code in the future. > -- Now lets try to add 3 hours
> select systimestamp, systimestamp + INTERVAL '3' hour from dual
SYSTIMESTAMP SYSTIMESTAMP+INTERVAL'3'HOUR ------------- ---------------------------- 09-DEC-11 18.39.53.042000000 +00:00 09-DEC-11 21.39.53.042000000 +00:00
> -- Note the indicator is always singular. > -- Now lets add 3 hours, 5 minutes 10.5 seconds
> select systimestamp, > systimestamp + INTERVAL '3' hour > + INTERVAL '5' minute > + INTERVAL '10.5' second > from dual
SYSTIMESTAMP SYSTIMESTAMP+INTERVAL'3'HOUR+INTERVAL'5'MINUTE+INTERVAL'10.5'SECOND ------------- ------------------------------------------------------------------- 09-DEC-11 18.39.53.042000000 +00:00 09-DEC-11 21.45.03.542000000 +00:00
> -- Spot on - and the precision is correct too > -- My mam/mum/mom* could read it and work out what was going on. > -- But she is an OCA** (* delete as applicable) (**not really) > -- There you go. Get your company to use this nomenclature and you're home free. > -- No more strange time-base calculations or implicit conversion errors. > -- It also works with DATE types too.
Original Script set echo on
-- Lets just set the date format explicitly before we start. alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; -- Lets start simple and add a day. select systimestamp, systimestamp + 1 from dual; -- Hang on, doing this the traditional Oracle way has implicitly cast -- the timestamp into a sysdate format. We need to convert it back. -- Lets try that again, but add 3 hours instead select systimestamp, to_timestamp( systimestamp + (3/24), 'DD-MON-YYYY HH24:MI:SS' ) from dual; -- So we have an implicit conversion, and we've lost the timestamp precision. -- Now lets add 3 hours, 5 minutes 10.5 seconds. select systimestamp, to_timestamp( systimestamp + (3/24) + (5/(24*60)) + (10.5/24/60/60), 'DD-MON-YYYY HH24:MI:SS') from dual; -- And we completely lost the .5 in the 10.5 as we've converted to a sysdate. -- It was rounded up. You might also have noticed that I have used some -- different date processing to calculate fractions of a day. -- There are many different way to calculate time in Oracle: -- 10 minutes can be 10/1440, 10/60/24, 10/(60*24), 0.00694, 600/86400 etc, -- It's all tricky to understand and standards vary from company to company, -- if the company actually has a standard. -- There is a better way, using the INTERVAL function. It goes like this: -- Lets start simple and add a day. select systimestamp, systimestamp + INTERVAL '1' day from dual; -- So far so good. And we haven't lost the data type either! -- No implicit conversion to break our code in the future. -- Now let's try to add 3 hours select systimestamp, systimestamp + INTERVAL '3' hour from dual; -- Note the indicator is always singular. -- Now lets add 3 hours, 5 minutes 10.5 seconds select systimestamp, systimestamp + INTERVAL '3' hour + INTERVAL '5' minute + INTERVAL '10.5' second from dual; -- Spot on - and the precision is correct too -- My mam/mum/mom* could read it and work out what was going on. -- But she is an OCA** (* delete as applicable) (**not really) -- There you go. Get your company to use this nomenclature and you're home free. -- No more strange time-base calculations or implicit conversion errors. -- It also works with DATE types too.
Neil Chandler's DB Blog 
When I tested this in 10g, I found that the interval approach was about 50% slower than the fractional day approach.
LikeLike
Alistair,
I can’t see how there would be a performance difference. It’s just some straightforward arithmetic. Here’s a quick test, performing a date calc 1,000,000 times.
declare
strt timestamp := systimestamp;
fin timestamp := systimestamp;
diff varchar2(100) := ‘ ‘;
scratch date := sysdate;
begin
strt := systimestamp;
for loop_counter in 1..1000000
loop
scratch := sysdate + 1/86400;
end loop;
fin := systimestamp;
diff := to_char(fin – strt);
dbms_output.put_line(‘Duration 1 : oldstyle : ‘||diff);
strt := systimestamp;
for loop_counter in 1..1000000
loop
scratch := sysdate + INTERVAL ‘1’ second;
end loop;
fin := systimestamp;
diff := to_char(fin – strt);
dbms_output.put_line(‘Duration 2 : newstyle : ‘||diff);
strt := systimestamp;
for loop_counter in 1..1000000
loop
scratch := sysdate + 1/86400;
end loop;
fin := systimestamp;
diff := to_char(fin – strt);
dbms_output.put_line(‘Duration 3 : oldstyle : ‘||diff);
strt := systimestamp;
for loop_counter in 1..1000000
loop
scratch := sysdate + INTERVAL ‘1’ second;
end loop;
fin := systimestamp;
diff := to_char(fin – strt);
dbms_output.put_line(‘Duration 4 : newstyle : ‘||diff);
end;
Duration 1 : oldstyle : +000000000 00:00:01.500000000
Duration 2 : newstyle : +000000000 00:00:01.484000000
Duration 3 : oldstyle : +000000000 00:00:01.344000000
Duration 4 : newstyle : +000000000 00:00:01.922000000
Duration 1 : oldstyle : +000000000 00:00:01.578000000
Duration 2 : newstyle : +000000000 00:00:01.469000000
Duration 3 : oldstyle : +000000000 00:00:01.484000000
Duration 4 : newstyle : +000000000 00:00:01.484000000
Nothing in it. Can you show me how you got the performance problem?
sysdate + INTERVAL ‘3’ hour+INTERVAL ‘5’ minute+INTERVAL ‘5’ second
takes about the same amount of time as
sysdate + (3/24) + (5/1440) + (5/86400)
The only way I can generate a discrepancy is to compare:
sysdate + INTERVAL ‘3’ hour+INTERVAL ‘5’ minute+INTERVAL ‘5’ second
with
sysdate + 11105/86400
That’s not really a fair comparison as you are doing less maths (which you will have had to perform elsewhere to convert to seconds anyway). And you’re still losing precision for timestamps.
regards
Neil
[note: the database used for the timings was not completely idle, so other factors may have interfered with absolute accuracy]
LikeLike
I think your code is optimised so the calculation is only performed once. This shows that the interval method is twice as fast – the opposite of what I remembered.
declare
strt timestamp := systimestamp;
fin timestamp := systimestamp;
diff varchar2(100) := ‘ ‘;
scratch date := sysdate;
begin
strt := systimestamp;
scratch := sysdate;
for loop_counter in 1..1000000
loop
scratch:=scratch+1/86400;
end loop;
fin := systimestamp;
diff := to_char(fin – strt);
dbms_output.put_line(‘Duration 1 : oldstyle : ‘||diff);
strt := systimestamp;
scratch := sysdate;
for loop_counter in 1..1000000
loop
scratch := scratch + INTERVAL ‘1’ second;
end loop;
fin := systimestamp;
diff := to_char(fin – strt);
dbms_output.put_line(‘Duration 2 : newstyle : ‘||diff);
strt := systimestamp;
scratch:=sysdate;
for loop_counter in 1..1000000
loop
scratch := scratch + 1/86400;
end loop;
fin := systimestamp;
diff := to_char(fin – strt);
dbms_output.put_line(‘Duration 3 : oldstyle : ‘||diff);
strt := systimestamp;
scratch := sysdate;
for loop_counter in 1..1000000
loop
scratch := scratch + INTERVAL ‘1’ second;
end loop;
fin := systimestamp;
diff := to_char(fin – strt);
dbms_output.put_line(‘Duration 4 : newstyle : ‘||diff);
end;
Duration 1 : oldstyle : +000000000 00:00:02.031146000
Duration 2 : newstyle : +000000000 00:00:01.430190000
Duration 3 : oldstyle : +000000000 00:00:02.238778000
Duration 4 : newstyle : +000000000 00:00:01.481589000
LikeLike
I’ve played around with my test case, and got it adding and subtracting random amounts, and got it running via DUAL too (which is massively slower as is has to handle all of the PL/SQL to SQL context switching) and I can’t persuade the system to have a noticable consistent difference between either option (rel: 10.2.0.5).
Given the upsides of INTERVAL processing, I can’t find any rationale for continuing with the old method.
Neil.
LikeLike