SQL如何减去浮点时间和字符串时间

Question

表DB2

ID       HOURS        HOURSMINUTES       
1000     480.5        30:30:00

我想获得小时数-小时数

ID       HOURS - HOURSMINUTES
1000       450.0

HOURS是以HOURS为单位的浮动值，因此为480.5小时。HOURSMINUTES是字符串值：30:30:00（30小时30分00秒）

怎么做减法？

这是我的完整表达式，因为我从两个表中获取值（我以这种格式获取它们，但不能减法）。我已经从两种时间戳格式中减去了HOURS——结果是浮动的。累积时间是字符串值。

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 

当我尝试在下面插入解决方案时，我遇到了一个错误。

    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 

我也尝试Kapil版本：

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 

Answer 1

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

Answer 2

试试这个：

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

Answer 3

以下是一个在兼容模式下使用DB2 for z/OS 9.1版本的解决方案：

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;

这给出了CCD_ 1的结果。

如果有可用的LOCATE_IN_STRING，则可以使用它来简化在HMS字符串中查找第n个:的过程。