SYSTIMESTAMP INTERVAL Processing

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.