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
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