Neil Chandler's DBA Blog

A resource for Database Professionals

SYSTIMESTAMP INTERVAL Processing

with 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.
About these ads

Written by Neil Chandler

12th December 2011 at 07:59

4 Responses

Subscribe to comments with RSS.

  1. When I tested this in 10g, I found that the interval approach was about 50% slower than the fractional day approach.

    Alistair Wall

    12th December 2011 at 12:24

  2. 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]

    Neil Chandler

    12th December 2011 at 13:39

  3. 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

    Alistair Wall

    13th December 2011 at 18:00

  4. 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.

    Neil Chandler

    14th December 2011 at 09:03


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 27 other followers

%d bloggers like this: