Convert Decimal Number to time

0
votes

How to convert decimal number to time. suppose if I have a decimal number 2.56 by converting it into the time it would be 02:33:36.

-->CREATE TABLE t1(COL1 INTEGER NOT NULL,COL2 DECIMAL(10,2));

-->insert into t1(col1,clo2 ) values(1024 , 2.56 ), (1024 , 4.23 ), (1024 , 1.67 ), (1024 , 0.56 )

1
db2

1 Answers

0
votes
$ db2 "select i, j
  ,  time('00.00.00') 
     + int(j) hours 
     + int(mod(j,1)*60) minutes 
     + int(mod(mod(j,1)*60*60,60)) seconds as time
  from table( values(1024 , 2.56 ), (1024 , 4.23 )
                  , (1024 , 1.67 ), (1024 , 0.56 )
            ) a(i,j)"

I           J     TIME    
----------- ----- --------
       1024  2.56 02:33:36
       1024  4.23 04:13:48
       1024  1.67 01:40:12
       1024  0.56 00:33:36

  4 record(s) selected.

BTW you should store time values a TIME datatype not DECIMAL