How to Get Difference from Two Timestamp in Db2

How to get difference from two timestamp in DB2?

It's easy, by converting the timestamp to hours as follows:

(24*DAYS(last_modified_datetime)+MIDNIGHT_SECONDS(last_modified_datetime)/3600)
-
(24*DAYS(create_datetime)+MIDNIGHT_SECONDS(create_datetime)/3600)

How to find the number of hours between two timestamps in Db2

The HOURS_BETWEEN function is the cleanest way to find the number of full hours between two timestamps in DB2

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0061478.html

The HOURS_BETWEEN function returns the number of full hours between the specified arguments.

For example

VALUES 
(    HOURS_BETWEEN('2020-03-17-10.58.25', '2019-03-16-16.59.26')
,    HOURS_BETWEEN('2020-03-19-01.47.26', '2019-03-18-08.57.05')
)

returns

1   |2   
----|----
8801|8800

Note that the value is negative if the first value is less than the second value in the function

Also note that this function does not exist in version of Db2 (for LUW) lower than 11.1

Calculate SQL db2 date time difference between below

Try one of these Db2 functions

DAYS_BETWEEN    (h,l)
HOURS_BETWEEN   (h,l)
MINUTES_BETWEEN (h,l)
MONTHS_BETWEEN  (h,l)
SECONDS_BETWEEN (h,l)
WEEKS_BETWEEN   (h,l)
YEARS_BETWEEN   (h,l)
YMD_BETWEEN     (h,l)

The functions return whole numbers. Put the higher value first to get a +ve number out.

Calculating how many days are between two dates in DB2?

I think that @Siva is on the right track (using DAYS()), but the nested CONCAT()s are making me dizzy. Here's my take.

Oh, there's no point in referencing sysdummy1, as you need to pull from a table regardless.

Also, don't use the implicit join syntax - it's considered an SQL Anti-pattern.

I'be wrapped the date conversion in a CTE for readability here, but there's nothing preventing you from doing it inline.

WITH Converted (convertedDate) as (SELECT DATE(SUBSTR(chdlm, 1, 4) || '-' ||
                                               SUBSTR(chdlm, 5, 2) || '-' ||    
                                               SUBSTR(chdlm, 7, 2))
                                   FROM Chcart00
                                   WHERE chstat = '05')

SELECT DAYS(CURRENT_DATE) - DAYS(convertedDate)
FROM Converted

Difference between time columns in SQL

In DB2 the result of the subtraction of two TIME values is a duration, a DECIMAL in the form HHMMSS. So, subtracting '20:31:00' from '23:00:00' gives 22900, meaning 2 hours 29 minutes 00 seconds. Subtracting a later time '23:59:00' from an earlier time '00:53:00' obviously gives a negative value -230600.

---

Related Topics