Timestamp Arithmetic and Well Known Mistakes

Published on: 2020-07-28 by Mustafa Comment

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:

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

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

Basically that is all if you use add and subtract of course we […]

Frequent and Fast Refresh Materialized Views has a Big Problem!

Published on: 2020-07-252020-07-25 by Mustafa 2 Comments

Hi, While I was checking V$SQL_PLAN view for a whole different reason, I realized that some of the cursors has incredibly high child cursors. I started to dig and finally I find the problem. My customer have a few frequently fast refreshed materialized views. One of them is refreshed every 10 seconds (you might say […]

CSV Data in Clob to Columns Using Select

Published on: 2020-07-142020-07-14 by Mustafa Comment

Hi, Thanks to one of my customers, I am dealing with csv data in clob lately a lot. so they asked me if I can get a csv data in a clob column as separated columns using select statement. They wanted to show this data on their applicaitons. I suggested different methods but they said […]

Is It Possible to Change SQL Statement in the Database?

Published on: 2020-07-042020-07-04 by Mustafa Comment

YES! Hi, today I want to write about something which has been asked for many times to me. Let’s say you have an application and it is running very poorly designed sql statements in your database and you are really bored having fights with development teams to ask a change about the sql. Well, in […]

V$SqlStats vs V$SqlStats_Plan_Hash, different columns?

Published on: 2020-07-012020-07-01 by Mustafa Comment

Hi there, V$SQLSTATSis a beautiful view for information about sql statements. you might say v$sql has that information too which is correct but V$SQLSTATSis much more faster than v$sql and keeps data about sql statements for a longer time than v$sql. https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQLSTATS.html However, the V$SQLSTATS view differs from V$SQL and V$SQLAREA in that it is faster, more scalable, and has a […]