Calculating the difference between SQL timestamp columns

Document ID : KB000009508
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

CA Datacom SQL provides special scalar functions for working with dates and times. However, there is no built-in function for calculating the difference between two timestamp values (a single column containing both date and time information).

How can I make this calculation accurately?

Instructions:

To calculate the difference between two timestamps (whether in explicitly defined columns or in intermediate results values derived from other calculations), here are two possibilities:

  1. You can produce a result with the number of days between the two timestamps, along with the time difference in hours, minutes and seconds;
  2. You can produce a result with the date difference in years, months and days, along with the time difference in hours, minutes and seconds.

Below are some sample query segments for the DBSQLPR program that assume two columns - TS1 and TS2 - are defined as timestamps. You can easily change the order of the result field components as desired. In these samples, TS2 is the minuend and is the higher (or later) value, and TS1 is the subtrahend to be subtracted from TS2.

In the first example, calculate the difference between TS1 and TS2 with the result in the format of totaldays-hours.minutes.seconds:

SELECT
--  EXTRACT DATE AND TIME FROM TIMESTAMP VALUES AS YR-MO-DY @ HR-MN-SEC
    CHAR(DATE(TS1)) || ' @ ' ||   CHAR(TIME(TS1),JIS)  AS "TS1 VALUE"
   ,CHAR(DATE(TS2)) || ' @ ' ||   CHAR(TIME(TS2),JIS)  AS "TS2 VALUE"
--  -----------------------------------------------------------------7--
--  CALCULATE DIFFERENCE BETWEEN TIMESTAMPS, AS DAYS-HR.MN.SEC
  ,SUBSTR(DIGITS(DAYS(TS2) - DAYS(TS1) -
        CASE WHEN TIME(TS2) < TIME(TS1) THEN 1 ELSE 0 END),7)
   || '-' ||
    CHAR(TIME('00.00.00') + (TIME(TS2) - TIME(TS1)))
                                               AS "ELAPSED DDDD-H.M.S"
...(FROM clause and any others)

Here is a possible result of the query:

TS1 VALUE             TS2 VALUE             ELAPSED DDDD-H.M.S
CHAR(21) NOT NULL     CHAR(21) NOT NULL     CHAR(13)          
_____________________ _____________________ __________________
2017-08-08 @ 08.34.50 2017-10-11 @ 11.08.51  0064-02.34.01    

 

In the next example, calculate the difference between TS1 and TS2 with the result in the format of years.months.days--hours.minutes.seconds:

SELECT
--  EXTRACT DATE AND TIME FROM TIMESTAMP VALUES AS YR-MO-DY @ HR-MN-SEC
   CHAR(DATE(TS1)) || ' @ ' ||   CHAR(TIME(TS1),JIS)  AS "TS1 VALUE"
  ,CHAR(DATE(TS2)) || ' @ ' ||   CHAR(TIME(TS2),JIS)  AS "TS2 VALUE"  
--  -----------------------------------------------------------------7--
--  CALCULATE DIFFERENCE BETWEEN TIMESTAMPS, AS YR.MO.DY--HR.MN.SEC      
--                                                                      
  ,SUBSTR(DIGITS(DATE(TS2) - (DATE(TS1) +                                
        (CASE WHEN TIME(TS2) < TIME(TS1) THEN 1 ELSE 0 END) DAY)),3,2)      
    || '.' ||
   SUBSTR(DIGITS(DATE(TS2) - (DATE(TS1) +
        (CASE WHEN TIME(TS2) < TIME(ST1) THEN 1 ELSE 0 END) DAY)),5,2)
   || '.' ||
   SUBSTR(DIGITS(DATE(TS2) - (DATE(TS1) +
       (CASE WHEN TIME(TS2) < TIME(TS1) THEN 1 ELSE 0 END) DAY)),7,2)
   || '--' ||
   CHAR(TIME('00.00.00') + (TIME(TS2) - TIME(TS1)))
                                            AS "ELAPSED Y.M.D--H.M.S"  
...(FROM clause and any others)

Again, with the same values as seen earlier, this is a possible result:

TS1 VALUE             TS2 VALUE             ELAPSED Y.M.D--H.M.S  
CHAR(21) NOT NULL     CHAR(21) NOT NULL     CHAR(18)            
_____________________ _____________________ ____________________  
2017-08-08 @ 08.34.50 2017-10-11 @ 11.08.51  00.02.03--02.34.01  

Some points to note regarding the above query segments:

  • When subtracting two dates using the DATE scalar function, the result will be a duration of the type DECIMAL(8,0), with a format of yyyymmdd. In the case of the above sample dates, this duration would be 00000203.
  • Time durations will be of the type DECIMAL(6,0), with a format of hhmmss. By using the CHAR scalar function, the output format will be the same as the format of the second argument (TS1, in our case, with a default ISO format of hh.mm.ss)
  • The time calculation incorporates TIME('00.00.00') added to the values to adjust for the second argument being less than the first.
  • Since the date durations must be handled separately from the time durations, if the time part of the second argument is less than the first, a one-day difference is adjusted in the date calculations to accommodate the "borrowing" of a day for the time duration.

Attached is a sample file - TEC580613.zip - used with the DBSQLPR program to calculate the difference between the MUF startup time and the current time, both timestamp columns. This query demonstrates both of the above format durations.

Additional Information:

For more information about the use of date and time calculations in SQL queries, please refer to the webpage below for your version.

Version 15.1:

Reference / SQL Reference / SQL Expressions /Arithmetic Operations for Dates, Times, and Timestamps

Version 15.0:

Reference SQL Reference / SQL Expressions /Arithmetic Operations for Dates, Times, and Timestamps

Version 14.0:

SQL User Guide / Expressions Arithmetic Operations for Dates, Times, and Timestamps

As always, please contact CA Technologies support for CA Datacom if you have further questions.

 

File Attachments:
TEC580613.zip