CODEWITHSUNDEEP
sqldb2db2-400
Geoff_S
Geoff_S

Reputation: 5105

Rounding with midnight seconds in DB2

Maybe this is more simple than I realize, but I'm trying to simply look at records like the ones below and add up the timestamps but for grouping where the difference is only in seconds, my result set is 0 but I'd like to get this in a decimal for second counts as well.

I'm using DB2 on AS400 iSeries Version 7

Sample data

JOB_STATUS   |   JOB_STATUS_TIME
---------------------------------------------
 P                2019-10-02  08:47:12.362261
 P                2019-10-02  08:47:22.362261 
 P                2019-10-02  08:47:32.362261
 P                2019-10-02  08:47:42.362261
 P                2019-10-02  08:47:52.362261

Query: 

SELECT
SUM
(
 CASE A1.JOB_STATUS WHEN 'P' THEN
(DAYS(A2.JOB_STATUS_TIME) - DAYS(A1.JOB_STATUS_TIME)) * 86400
 + MIDNIGHT_SECONDS(A2.JOB_STATUS_TIME) - 
 MIDNIGHT_SECONDS(A2.JOB_STATUS_TIME)
 END
 ) / 60 AS ACTIVE_MINUTES 
FROM SCHEMA.TABLE;

I tried adding ROUND({sum query}, 2) but that didn't work for me, still returned zero

Upvotes: 0

Views: 385

Answers (2)

nfgl
nfgl

Reputation: 3212

It's a bit off topic, but I think what you do can be done with 

TIMESTAMPDIFF(2, MAX(JOB_STATUS_TIME) - MIN(JOB_STATUS_TIME) )

Upvotes: 0

Paul Vernon
Paul Vernon

Reputation: 3901

If all the columns and values used in a division operation are INGEGER (or BIGINT or SMALLINT) then the division will be an integer division.

You need to cast something to DECIMAL or use a decimal, float of DECFLOAT value to get decimal division. E.g. try dividing by 60.0

SELECT
   SUM(CASE A1.JOB_STATUS WHEN 'P' THEN
        (DAYS(A2.JOB_STATUS_TIME) - DAYS(A1.JOB_STATUS_TIME)) * 86400
       + MIDNIGHT_SECONDS(A2.JOB_STATUS_TIME)
       - MIDNIGHT_SECONDS(A1.JOB_STATUS_TIME)
 END
 ) / 60.0 AS ACTIVE_MINUTES 
FROM
   SCHEMA.TABLE

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/db2/rbafz2intoper.htm

If both operands of an arithmetic operator are integers with zero scale, the operation is performed in binary, and the result is a large integer unless either (or both) operand is a big integer, in which case the result is a big integer. Any remainder of division is lost

Upvotes: 3

Related Questions