SYSTIMESTAMP INTERVAL Processing

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

UKOUG 2011

Well, I’m back at work today missing the excellent final day of the UKOUG 2011 (@UKOUG  #UKOUG2011), but frankly I’m worn out.

After watching some of the most fantastic presentations by the likes of Doug Burns, Jonathan Lewis, Greg Rahn, Tanel Poder and many others**, my brain is full. I couldn’t absorb another fact.

A similar crowd, too numerous to mention, then helped me destroy the information that I had just learned, through the medium of good British Real Ale and Whisky.

Technical blogs to follow, once my brain cools down and my liver recovers.

Neil.

**A special mention to Thomas Presslie for pouring shots of The Balvenie Scottish Single Malt Whisky into me to demonstrate DataGuard Fast Start Failover, at 10:30am. Transactional Whisky indeed. Not sensible but good fun and a novel way to represent a commit – I’m on the left http://lockerz.com/s/162506859 (thanks for the Photo, Doug)

Industry Experience

I don’t get it. Why do so many jobs and contracts seem to insist upon having experience in a particular industry when, in the overwhelming majority of cases, the specific industry in which we work has no bearing upon the nature of our work.

I have worked across many industries, but each time I talk to a recruitment agent I get similar questions: “Have you worked in X industry?”, “I won’t put you forward for Y unless you have worked for Z”.

It’s the wrong question. Have I worked in Media? Investment Banking? Accountancy? Property? Logistics? It doesn’t matter. No, really. It doesn’t. I have worked in all of those industries and a few more besides, and the nature of the industry was largely irrelevant. A friend recently suggested that you need Investment Banking experience so you understand the inordinate bureaucracy and dreadful boredom that come with working for an Investment Bank. A little unkind, but I know where he’s coming from.

What is relevant is the type and nature of systems with which you are working. Are they mission critical? Zero downtime? Very High Transaction rate? Enormous Data Warehouses? Hundreds or Thousands of databases? These questions have relevance. A high transaction rate OLTP in a Bank is very similar to a high transaction rate OLTP Web Retailer.  The challenge with these systems is a different to that of an enormous data warehouse, but it’s still fundamentally an RDBMS. Data is data is data. We don’t need industry experience – it doesn’t help us in the same way as it helps Business Analysts or Project Managers or even Developers.

The recruitment problem for DBA’s is that recruiters don’t know the difference between OLTP and Data Warehousing; a large proportion simply keyword match (the great ones don’t! – and there are genuinely great recruiters out there, in small numbers) so you need to ensure you have all of the relevant keywords on your CV – I have even been asked to amend my CV to put Word and Excel on there! WTF? Unfortunately you also need to be careful, otherwise you’re probably getting job adverts sent through for Cobol Programmers, Websphere Guru’s and all manner of support and helpdesk staff. I removed IBM Assembler Programmer from my CV about 10 years ago, although I suspect there are not too many jobs left for that skill set now.