Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

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:

1
2
3
4
5
6
7
8
9
10
11
12
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 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.

1
2
3
4
5
6
7
8
9
10
11
12
Select Systimestamp "TODAY",
       Systimestamp + 1 "TOMORROW", --add 1 day
       Systimestamp - 1 "YESTERDAY", --subtract 1 day
       Systimestamp + (1/24) "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour
       Systimestamp - (1/24) "ONE HOUR BEFORE", -- remove 1 hour
       Systimestamp + (1/(24*60)) "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently
       Systimestamp + (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:14:21.490000 +03:00   2020-07-29 14:14:21 2020-07-27 14:14:21 2020-07-28 15:14:21 2020-07-28 13:14:21 2020-07-28 14:15:21 2020-07-28 14:14:22

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:

1
2
3
4
5
6
7
Select Dump(sysdate) ,
       Dump(Systimestamp + 0)
From   Dual;
 
DUMP(SYSDATE)                      DUMP(SYSTIMESTAMP+0)
---------------------------------- ----------------------------------
Typ=13 Len=8: 228,7,7,28,14,24,5,0 Typ=13 Len=8: 228,7,7,28,14,24,5,0

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:

An error has occurred. Please try again later.

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:

1
2
3
Select *
From   Orders
Where  Created_timestamp >= (Systimestamp -1)

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.

1
2
3
4
5
6
7
8
9
10
Select Systimestamp "TODAY",
       Systimestamp + Interval '1' Day "TOMORROW", --add 1 day
       Systimestamp + Interval '1' HOUR "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour
       Systimestamp + Interval '1' MINUTE "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently
       Systimestamp + Interval '1' SECOND "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequently
From   Dual;
 
TODAY                             TOMORROW                             ONE HOUR LATER                       ONE MINUTE LATER                     ONE SECOND LATER
--------------------------------- ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------
2020-07-28 14:36:57.099000 +03:00 2020-07-29 14:36:57.099000000 +03:00 2020-07-28 15:36:57.099000000 +03:00 2020-07-28 14:37:57.099000000 +03:00 2020-07-28 14:36:58.099000000 +03:00

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

1
2
3
4
5
6
Select NumToDSinterval(1, 'DAY'), NumToDSinterval(1, 'HOUR')
From dual;
 
NUMTODSINTERVAL(1,'DAY')      NUMTODSINTERVAL(1,'HOUR')
----------------------------- ---------------------------------------------------------------------------
+000000001 00:00:00.000000000 +000000000 01:00:00.000000000

so you can use:

1
2
3
4
5
6
Select Systimestamp "TODAY",
       Systimestamp + NumToDSinterval(1, 'Day') "TOMORROW", --add 1 day
       Systimestamp + NumToDSinterval(1, 'HOUR') "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour
       Systimestamp + NumToDSinterval(1, 'MINUTE') "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently
       Systimestamp + NumToDSinterval(1, 'SECOND') "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequently
From   Dual;

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.

11g 12c 18c 19c Development SQL / PLSQL datedate arithmeticinterval typetimestamptimestamp arithmetictimestamp with time zone

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

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

Recent Posts

  • Sessions with details
  • DBMS_PIPE Connecting the Sessions
  • DBCA Templates and Dangerous “General Purpose” Template
  • NON-CDB to PDB Migration Horror
  • 23c is Finally Here!
  • Not Deploying is also Important!
  • Damaged rowid is referenced! Health Monitor
  • Old Obsolete Backup is not Deleted

Recent Comments

  • Mustafa on DBMS_PIPE Connecting the Sessions
  • Hemant K Chitale on DBMS_PIPE Connecting the Sessions
  • Mustafa on DBMS_PIPE Connecting the Sessions
  • Hemant K Chitale on DBMS_PIPE Connecting the Sessions
  • Phong on ORA-00942: table or view does not exist and sequences as default value

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23c
  • Administration
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • Sessions with details
  • DBMS_PIPE Connecting the Sessions
  • DBCA Templates and Dangerous “General Purpose” Template
  • NON-CDB to PDB Migration Horror
  • 23c is Finally Here!
  • Not Deploying is also Important!
  • Damaged rowid is referenced! Health Monitor
  • Old Obsolete Backup is not Deleted
  • Autoupgrade doesn’t Upgrade Timezone Version While RU Patching
  • Unique Columns For V$Sql

Archives

  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • Sessions with details
  • DBMS_PIPE Connecting the Sessions
  • DBCA Templates and Dangerous “General Purpose” Template
  • NON-CDB to PDB Migration Horror
  • 23c is Finally Here!
  • Not Deploying is also Important!
  • Damaged rowid is referenced! Health Monitor
  • Old Obsolete Backup is not Deleted
  • Autoupgrade doesn’t Upgrade Timezone Version While RU Patching
  • Unique Columns For V$Sql
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset="utf-8"`
©2023 Keep Learning Keep Living | WordPress Theme by SuperbThemes