Got this little SQL quiz from a colleague.  How to add or subtract exactly 1 second from a Timestamp?  Sounded simple enough at first blink, but was a bit trickier than expected.

If the data type had been a Date, we knew that we could add or subtract days, minutes or seconds using + or –

• sysdate + 1 to add one day
• sysdate - (1 / 24) to subtract one hour
• sysdate + (1 / 86400) to add one second

Would the same arithmetic work with Timestamp as with Date?

Let’s test it out with the following query

The first result line shows us the system time down to fractions of seconds. The second result line shows the result as Date (as used for date calculation) meaning now that the granularity is reduced down to a second.  

By using the PL/SQL dump() function, we can confirm this with the following query

Where typ=13 is a runtime representation for Date.

So how can we increase the precision to include fractions of second? After investigating it a bit, we found out that the interval data type INTERVAL DAY TO SECOND could be used with the result of addition or subtraction being a Timestamp. Let’s try again our first query again, now using the interval data type.

Yes, it worked!

To finish the story, here is one example showing how to specify an interval of 2 days, 6 hours, 30 minutes, 4 seconds and 111 thousands of a second.

INTERVAL ‘2 6:30:4.111’ DAY TO SECOND(3)