Math with Timestamp
- by Knut Vatsendvik
table.sql {
border-width: 1px;
border-spacing: 2px;
border-style: dashed;
border-color: #0023ff;
border-collapse: separate;
background-color: white;
}
table.sql th {
border-width: 1px;
padding: 1px;
border-style: none;
border-color: gray;
background-color: white;
-moz-border-radius: 0px 0px 0px 0px;
}
table.sql td {
border-width: 1px;
padding: 3px;
border-style: none;
border-color: gray;
background-color: white;
-moz-border-radius: 0px 0px 0px 0px;
}
.sql-keyword {
color: #0000cd;
background-color: inherit;
}
.sql-result {
color: #458b74;
background-color: inherit;
} 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 SELECT systimestamp , systimestamp + (1 / 86400) FROM dual; ---------- 03.05.2010 22.11.50,240887 +02:00 03.05.2010 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 SELECT dump(systimestamp) , dump(systimestamp + (1 / 86400)) FROM dual; ---------- Typ=188 Len=20: 218,7,5,4,8,53,9,0,200,46,89,20,2,0,5,0,0,0,0,0 Typ=13 Len=8: 218,7,5,4,10,53,10,0 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. SELECT systimestamp, systimestamp + INTERVAL '0 00:00:01.0' DAY TO SECOND(1) FROM dual; ---------- 03.05.2010 22.58.32,723659000 +02:00 03.05.2010 22.58.33,723659000 +02:00 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)
