Timestamp difference is frequently required operation to determine duration between two events occurred at Time T1 & Time T2. The required duration can be in days, hours, minutes or even in seconds.
CREATE volatile table dateTable
( DateTimeField1 TIMESTAMP(0),
DateTimeField2 TIMESTAMP(0),
DateTimeField3 TIMESTAMP(0),
DateTimeField4 TIMESTAMP(0)
)
ON COMMIT PRESERVE ROWS;
INSERT INTO dateTable VALUES ('2018-08-05 11:11:23','2018-08-03 04:15:23','2018-08-04 12:11:23','2018-08-04 13:13:53');
SELECT CAST('2017-07-18' AS DATE) End_Dt, CAST('2009-06-10' AS DATE) Start_Dt, End_Dt-Start_Dt;
Output:
End_Dt Start_Dt (End_Dt-Start_Dt)
---------- ---------- -----------------
2017-07-18 2009-06-10 2960
SELECT (DateTimeField1 - DateTimeField2) DAY(4) to SECOND(4) FROM dateTable;
Output:--> 2 6:56:00
Above output is 2 days, 6 hours, 56 minutes & 0 seconds
SELECT (DateTimeField1 - DateTimeField2) DAY(4) to SECOND(4) FROM dateTable;
Output:--> 54:56:00
Above output is 54 hours, 56 minutes & 0 seconds
SELECT (DateTimeField1 - DateTimeField2) MINUTE(4) to SECOND(4) FROM dateTable;
Output:--> 3296:00
Above output is 3296 minutes & 0 seconds
SELECT (DateTimeField1 - DateTimeField2) SECOND(4) FROM dateTable;
Output:--> will fail since timestamp difference(197,760) is more than 9999 seconds
SELECT (DateTimeField4 - DateTimeField3) SECOND(4) FROM dateTable;
Output:--> 3750
Above output is 3750 seconds
SELECT ((DateTimeField1 - DateTimeField2) DAY(4) to SECOND(4)) AS t1,
(EXTRACT(DAY from t1) +
EXTRACT(HOUR from t1)/24.0 +
EXTRACT(MINUTE from t1)/(24*60.0) +
EXTRACT(SECOND from t1)/(24*60.0*60.0)
) AS Required_Output
FROM dateTable;
Output: 2.288
SELECT ((DateTimeField1 - DateTimeField2) DAY(4) to SECOND(4)) AS t1,
(EXTRACT(DAY from t1)*24 +
EXTRACT(HOUR from t1) +
EXTRACT(MINUTE from t1)/(60.0) +
EXTRACT(SECOND from t1)/(60.0*60.0)
) AS Required_Output
FROM dateTable;
Output: 54.933
SELECT ((DateTimeField1 - DateTimeField2) DAY(4) to SECOND(4)) AS t1,
(EXTRACT(DAY from t1)*(24*60.0) +
EXTRACT(HOUR from t1)*60.0 +
EXTRACT(MINUTE from t1) +
EXTRACT(SECOND from t1)/60.0
) AS Required_Output
FROM dateTable;
Output: 3296.00
SELECT ((DateTimeField4 - DateTimeField3) DAY(4) to SECOND(4)) AS t1,
(EXTRACT(DAY from t1)*(24*60*60) +
EXTRACT(HOUR from t1)*(60*60) +
EXTRACT(MINUTE from t1)*60 +
EXTRACT(SECOND from t1)
) AS Required_Output
FROM dateTable;
Output: 3750
Output is required in hours from combination of 4 timestamps. Here requirement to sum combination of 2 timestamp (DateTimeField1 + DateTimeField3) & (DateTimeField2 + DateTimeField4) and then subtract it from other.
In simple words, requirement is to find hours difference from
(DateTimeField1 + DateTimeField4) - (DateTimeField2 + DateTimeField3)
Above statement is equivalent to
(DateTimeField1 - DateTimeField2) + (DateTimeField4 - DateTimeField3)
SELECT ((DateTimeField1 - DateTimeField2) HOUR(4))+( (DateTimeField4 - DateTimeField4) HOUR(4)) required_output
FROM dateTable;
SELECT
(DateTimeField1 - DateTimeField2) DAY(4) to SECOND(4) t1,
(DateTimeField4 - DateTimeField3) HOUR(4) to SECOND(4) t2,
EXTRACT(HOUR from t1)*24 + EXTRACT(HOUR from t1) + EXTRACT(MINUTE from t1)/60.0 + EXTRACT(SECOND from t1)/(60.0*60.0) t1_HOUR,
EXTRACT(HOUR from t2)+EXTRACT(MINUTE from t2)/60.0 +EXTRACT(SECOND from t2)/(60.0*60.0) t2_HOUR,
t1_HOUR+t2_HOUR required_output
FROM dateTable;