Examples: Date/Time Arithmetic

The examples appearing below reference a table called tasks created with the following command:

CREATE TABLE tasks(task_no SMALLINT, task_name CHAR(10), 
  est_hrs_reqd SMALLINT, startdate DATE, enddate 
  DATE,starttime TIME, endtime TIME, audit_time 
  TIMESTAMP)

date1-date2

In XDB mode, this expression calculates the difference in days between the two dates date1 and date2 (in DB2 mode, subtraction of two dates results in a date duration). As an example (using the TUTORIAL tables), the query below finds the number of orders placed for part P2 during the past 30 days:

SELECT SUM(quantity)
  FROM orders, items
  WHERE TODAY - o_date < 30

time1-time2

In XDB mode, this expression calculates the difference in seconds between the two times time1 and time2. To find the number of minutes, divide the difference between times by 60. To find the number of hours, divide the difference between times by 3600. For example, to find the total number of hours elapsed between the two stored time values starttime and endtime, you could use the query:

SELECT (endtime-starttime)/3600
FROM tasks

timestamp1-timestamp2

In XDB mode and DB2 mode, this expression calculates the difference between the two timestamps timestamp1 and timestamp2. This difference is expressed as a timestamp duration, where the format yyyyxxddhhmmsszzzzzz defines the years (yyyy), months (xx), days (dd), hours (hh), minutes (mm), seconds (ss), and microseconds (zzzzzz) of the timestamp duration.

time1 + n

In XDB mode, calculates the time generated by adding n seconds to time time1. The parameter n can be any expression that evaluates to a number. To add minutes to a time, multiply the number of minutes by 60. To add hours to a time, multiply the number of hours by 3600. For example, to find out what the estimated end time will be, given an estimated task time in hours, you could use the query:

SELECT "Estimated hours to completion: ",
  starttime + (est_hrs_reqd * )
FROM tasks