1

I'm trying to calculate the time driffrence between two columns, the columns have data: DECIMAL

I´ve tried to mix with:

TIMESTAMPDIFF(16, CHAR(TIMESTAMP('1997-03-01-00.00.00') 
    - TIMESTAMP('1997-02-01-00.00.00')))

but the data makes it complicated, anyone have any idea, how to make it work with a DECIMAL?

I managed to make som string transformation and actually get the time right with this code, but now I try to calculate the diffrence between them but I´m stuck. 

cast( substr( right( '00' ||cTime, 4) ,1,2) || ':' ||substr( right( '00'  ||cTime, 4) ,3,2)  as time) as "changeTime", 
cast( substr( right( '00' ||iTime, 4) ,1,2) || ':' || substr( right( '00' ||iTime, 4) ,3,2)  as time) as "inTime"

This is the code I want to make the difference between,

I expect the output: 

| 10:27:00 | 10:30:00 | 3 |

If someone need more information, let me know so I can edit with more information. Any help would be appreciated.

Version: 7.3 DB2 on AS400

Roshni Kasliwal
  • 229
  • 1
  • 11
The.B
  • 361
  • 2
  • 11
  • Please, rewrite your post specifying sample data in these decimal columns and expected result. We see the expected result only at the moment... – Mark Barinstein Feb 03 '19 at 10:41
  • the sample data in the decimal column is | 1027 | 1030 | but the third coulmn is a expected result. and one more question, is it possible to get hours:minutes in the third coulmn? – The.B Feb 03 '19 at 18:57
  • Not the issue here, but note that `TIMESTAMPDIFF` _can only estimate the difference_ (because of the result of the subtraction), and any duration greater than 1 month is very likely to be off. – Clockwork-Muse Feb 05 '19 at 01:02

1 Answers1

2

There is a simple math calculation behind this. If you store times in dec(4) column in form of HHMM, the time difference between such times is simple:

Firstly, we calculate HOURS and MINUTES from such a value:

HHx = int(Cx)/100

MMx = MOD(int(Cx), 100)

Secondly, we calculate the difference in minutes:

DIFF=(HH2*60+MM2) - (HH1*60+MM1)

Finally, we calculate the difference in HOURS and MINUTES:

HOURS=DIFF/60

MINUTES=MOD(DIFF, 60)

Example:

select 
  digits(dec(int(c)/100, 2))||':'||digits(dec(mod(int(c), 100), 2))||':00' as cTime
, digits(dec(int(i)/100, 2))||':'||digits(dec(mod(int(i), 100), 2))||':00' as iTime
,     ((int(i)/100*60 + mod(int(i), 100)) - (int(c)/100*60 + mod(int(c), 100)))/ 60  as hours
, mod(((int(i)/100*60 + mod(int(i), 100)) - (int(c)/100*60 + mod(int(c), 100))), 60) as minutes
from table(values 
  (dec(1027, 4), dec(1030, 4))
, (dec(1027, 4), dec(1126, 4))
, (dec(1027, 4), dec(1128, 4))
) t (c, i)

The result is:

CTIME       ITIME    HOURS MINUTES
--------    -------- ----- -------
10:27:00    10:30:00     0       3
10:27:00    11:26:00     0      59
10:27:00    11:28:00     1       1
Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16