SQL how to subtract float time and string time?

Question

Table DB2

ID       HOURS        HOURSMINUTES       
1000     480.5        30:30:00

I want to get HOURS - HOURSMINUTES

ID       HOURS - HOURSMINUTES
1000       450.0

HOURS is float value in HOURS- so 480.5 hours. HOURSMINUTES is string value: 30:30:00 (30hours 30minutes 00 seconds)

How to subtract?

This is my full expression because I am getting values from two tables (I get them in this format but I can not subtract) . I am getting HOURS already as subtraction from two timestamp formats - result is in float. cumulativetime is string value.

select t1.id,
dec (( timestampdiff(
  4, 
  char(t1.actualfinish - t1.reportdate))/60.00),10,2) as HOURS,t2.cumulativetime as HOURSMINUTES
from t1
join t2 on t2.id=t1.id

When I try to insert solution below I am getting an error.

    select t1.id,
    dec (( timestampdiff(
      4, 
      char(t1.actualfinish - t1.reportdate))/60.00),10,2) as HOURS,t2.cumulativetime as HOURSMINUTES,

dec (( timestampdiff(
      4, 
      char(t1.actualfinish - t1.reportdate))/60.00),10,2)- cast(substr(t2.cumulativetime, 1, 2) as int) - 
      (cast(substr(t2.cumulativetime, 4, 2) as int) / 60.0) as diff
    from t1
    join t2 on t2.id=t1.id

I tried also Kapil version:

select t1.id,
    dec (( timestampdiff(
      4, 
      char(t1.actualfinish - t1.reportdate))/60.00),10,2) as HOURS,t2.cumulativetime as HOURSMINUTES,

(dec (( timestampdiff(
  4, 
  char(t1.actualfinish - t1.reportdate))/60.00),10,2) - (CAST(substr(t2.cumulativetime , 1, 2) AS float) + CAST(substr(t2.cumulativetime , 4, 2) AS float)/60 + CAST(substr(t2.cumulativetime , 7, 2) AS float)/3600)) as diff

from t1
    join t2 on t2.id=t1.id

can you try this? `select t1.id, dec (( timestampdiff(4, char(t1.actualfinish - t1.reportdate))/60.00),10,2) as HOURS, t2.cumulativetime as HOURSMINUTES, cast(substr(t2.cumulativetime, 1, 2) as int) as hhdif, (cast(substr(t2.cumulativetime, 4, 2) as int) / 60.0) as mmdiff from t1 join t2 on t2.id=t1.id` please post the output or error message — paul, Jul 12 '12 at 12:03
paul I succeded problem was that one value in my column was 512:30:30 so with substr (1,2) (4,2) it was taking the not valid value (":" was includeded). So how to solve this then? To always be applicable no meter how decimal places will take hours in HOURSMINUTES column? Thank you — Veljko, Jul 12 '12 at 12:10

score 1 · Answer 1 · answered Jul 12 '12 at 11:04

1

select HOURS - 
       cast(substr(HOURSMINUTES, 1, 2) as int) - 
      (cast(substr(HOURSMINUTES, 4, 2) as int) / 60.0) as diff

answered Jul 12 '12 at 11:04

juergen d

201,996
37
293
362

op tagged DB2, so `substr` instead of `substring` – paul Jul 12 '12 at 11:07
My HOURSMINUTES column in this case starts with 30 (two decimal places) but I have in table values with 1,2,3,4,5,6,7,8,9 decimal places then I can not use this query because ":" will be in part for subtraction as float value which is not valid – Veljko Jul 12 '12 at 12:07
how then to solve this to be unique with all values for hours in HOURSMINUTES column? – Veljko Jul 12 '12 at 12:07
1

Then you can use [this split function](http://stackoverflow.com/questions/1305804/split-a-varchar-in-db2-to-retrieve-a-value-inside) to find the correct parts of your time string. – juergen d Jul 12 '12 at 12:13

score 1 · Answer 2 · answered Jul 12 '12 at 11:13

1

Try this:

Select  (HOURS - (CAST(substr(HOURSMINUTES , 1, 2) AS float) + CAST(substr(HOURSMINUTES , 4, 2) AS float)/60 + CAST(substr(HOURSMINUTES , 7, 2) AS float)/3600)) as diff
From table

answered Jul 12 '12 at 11:13

Kapil Khandelwal

15,958
2
45
52

My HOURSMINUTES column in this case starts with 30 (two decimal places) but I have in table values with 1,2,3,4,5,6,7,8,9 decimal places then I can not use this query because ":" will be in part for subtraction as float value which is not valid – Veljko Jul 12 '12 at 12:07
how then to solve this to be unique with all values for hours in HOURSMINUTES column? – Veljko Jul 12 '12 at 12:07

score 1 · Answer 3 · answered Jul 12 '12 at 13:00

Here's a solution using DB2 for z/OS version 9.1 in compatibility mode:

select
  t.HRS
  - cast(substr(t.HMS, 1, locate(':', t.HMS) - 1) as FLOAT)
  - (cast(substr(t.HMS, locate(':', t.HMS) + 1, locate(':', t.HMS, locate(':', t.HMS) + 1) - locate(':', t.HMS) - 1) as FLOAT) / 60.0)
  - (cast(substr(t.HMS, locate(':', t.HMS, locate(':', t.HMS, locate(':', t.HMS) + 1)) + 1) as FLOAT) / 3600.0)
from
  (
    select
      cast(480.5 as float) as HRS
      , '333:44:55' as HMS
    from
      sysibm.SYSDUMMY1
  ) as t
with ur for read only;

This gives a result of 146.75138888888887.

If you have LOCATE_IN_STRING available, you can use that to simplify finding the n-th : in the HMS string.

SQL how to subtract float time and string time?

3 Answers3