Performing time arithmetic in SQL involves adding or subtracting a fraction of a day to a given date or timestamp. The logic is simple: to add a certain amount of time, you add a fraction of a day, and to subtract time, you subtract the corresponding fraction.
For instance, suppose you want to add or subtract 60 minutes from a given date. Since there are 1440 minutes in a day (24 hours * 60 minutes), you would add or subtract 60/1440 (or 1/24) of a day.
Here's a breakdown of the process:
To add time: You add the fraction of a day to the date.
To subtract time: You subtract the fraction of a day from the date.
Let's illustrate this with SQL queries:
-- Adding 60 minutes to a date
SELECT sysdate + (60/1440) AS "Time +60 minutes" FROM task WHERE ID='1076';
-- Subtracting 60 minutes from a date
SELECT sysdate - (60/1440) AS "Time -60 minutes" FROM task WHERE ID='1076';
Dealing with Seconds
But what if you need to work with seconds instead of minutes? The approach is analogous. Since there are 86,400 seconds in a day (24 hours * 60 minutes * 60 seconds), you would divide the number of seconds you want to add or subtract by 86,400.
For example, to add or subtract 120 seconds, you would use (120/86400).
The sample code described herein is provided on an "as is" basis, without warranty of any kind, to the fullest extent permitted by law. Oracle + NetSuite Inc. does not warrant or guarantee the individual success developers may have in implementing the sample code on their development platforms or in using their own Web server configurations.
Oracle + NetSuite Inc. does not warrant, guarantee or make any representations regarding the use, results of use, accuracy, timeliness or completeness of any data or information relating to the sample code. Oracle + NetSuite Inc. disclaims all warranties, express or implied, and in particular, disclaims all warranties of merchantability, fitness for a particular purpose, and warranties related to the code, or any service or software related thereto.
Oracle + NetSuite Inc. shall not be liable for any direct, indirect or consequential damages or costs of any type arising out of any action taken by you or others related to the sample code.
Do you have any other date arithmetic in ODBC NetSuite2.com data source? Feel free to share them here!
If you find this information useful, let us know by reacting or commenting on this post! Thank you!