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?
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:
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:
See the attached file below for a DBSQLPR program to calculate the difference between the MUF startup time and the current time, which are both timestamp columns. This query demonstrates both of the above format durations.
For more information about the use of date and time calculations in SQL queries, please refer to the documentation topic Arithmetic Operations for Dates, Times, and Timestamps
As always, please contact Broadcom support for Datacom if you have further questions.