Timestamp Arithmetic and Well Known Mistakes


Lately, I started to see many developer uses timestamp types (like timestamp or timestamp with time zone) wrong especially in time arithmetic.

So as a short reminder, adding and subtracting numbers to/from a DATE value will add/subtract number of days:

Basically that is all if you use add and subtract of course we have “INTERVAL” data type which I am about to explain.

This usage is not wrong if you work with DATE but if you are using TIMESTAMP types then there is a small side effect.

so adding and subtracting did their’s job but if you look carefully there is no fractional seconds! Because they are not TIMESTAMP anymore, they are DATE. adding ans subtracting on timestamp types will convert result into a DATE. Let’s check if they are really date:

dump is a function that shows you how oracle store that data internally. in this example you can see both Typ number are 13 and 13 is one of date type which Oracle uses and yes there are more than one date type in oracle but they are not visible to us. For example return type of SYSDATE function and a table date column has different types:

as you see table date column type number is 12 not 13. so they are different but it’s not relevant to us right now.

PS: just in case if you wonder how this data is a date:

Typ=13 Len=8: 228,7,7,28,14,27,25,0

(228+(256*7)) = 2020 and rest is just the month day hour minute and second.

if I returned to main subject adding to a timestamp type will convert it to a DATE and if you use this type in your sql like:

you will be comparing timestamp (created_timestamp column in orders table) with a DATE (systimestamp -1). of course these are convertible types but still Oracle needs to do a convert operation. to avoid that you can use INTERVAL data types.

I found INTERVAL types more clear to myself.

as you can see Interval type are so simple and clean. we just write what we want to add or subtract and result is base type what you pass (TIMESTAMP With Time Zone in my example).

this type of interval has a fault to me. you can not pass number of Day/Hour/… as a parameters. this means you can not use “Interval V_Number_of_Days Day” for example but of course there is a solution, INTERVAL functions NumToDSinterval and NumToYMinterval.

NumToDSinterval = Number To DaySecond Interval

NumToYMinterval = Number To Year Month Interval

so you can use:

and result will be same with INTERVAL ‘1’DAY/HOUR…. Also please remember that you can use interval types with DATEs too.

hope this helps you in coding.

wish you a great day.

Leave a Reply

Your email address will not be published. Required fields are marked *