29th of February and interval Mustafa, 2024-02-29 Hi, it has been a long time since I post something. I just relocated to a different country, the Netherlands, and I was dealing with so much paperwork and stuff. So far so good. Dutch people are so kind and warm, they help about everything. My first impression is amazing… Continue Reading
Timestamp Arithmetic and Well Known Mistakes Mustafa, 2020-07-28 Hi, 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: Oracle PL/SQL Select Sysdate "TODAY", Sysdate + 1 "TOMORROW", --add 1 day Sysdate - 1 "YESTERDAY", --subtract 1 day Sysdate + (1/24) "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour Sysdate - (1/24) "ONE HOUR BEFORE", -- remove 1 hour Sysdate + (1/(24*60)) "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently Sysdate + (1/(24*60*60)) "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequently From Dual; TODAY TOMORROW YESTERDAY ONE HOUR LATER ONE HOUR BEFORE ONE MINUTE LATER ONE SECOND LATER ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- 2020-07-28 14:07:38 2020-07-29 14:07:38 2020-07-27 14:07:38 2020-07-28 15:07:38 2020-07-28 13:07:38 2020-07-28 14:08:38 2020-07-28 14:07:39 123456789101112 Select Sysdate "TODAY", Sysdate + 1 "TOMORROW", --add 1 day Sysdate - 1 "YESTERDAY", --subtract 1 day Sysdate + (1/24) "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour Sysdate - (1/24) "ONE HOUR BEFORE", -- remove 1 hour Sysdate + (1/(24*60)) "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently Sysdate + (1/(24*60*60)) "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequentlyFrom Dual; TODAY TOMORROW YESTERDAY ONE HOUR LATER ONE HOUR BEFORE ONE MINUTE LATER ONE SECOND LATER------------------- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------2020-07-28 14:07:38 2020-07-29 14:07:38 2020-07-27 14:07:38 2020-07-28 15:07:38 2020-07-28 13:07:38 2020-07-28 14:08:38 2020-07-28 14:07:39 Basically that is all if you use add… Continue Reading