AS400/DB2—将基于整数的日期和时间转换为时间戳

Question

我有4个用户输入:startDate, endDate, startTime, endTime。

数据库中的日期和时间设置与我以前使用的任何设置都不同。日期是自1900年12月31日以来的天数。一个整数。例如，2015年10月16日，将表示为41927。

时间是ISO格式的-没有小数，所以也是整数。所以08:00:00是80000。19:00:00是190000。它基本上是hhmmss -但有时你只有一个h。

我试图将日期和时间过滤到范围。有一天，我只是看时间，这工作得很好:

SELECT * FROM database WHERE day = 41927 AND time > startTime and time < endTime

如果你严格地看天，它也可以工作:

SELECT * FROM database WHERE day > startDate AND day < endDate

当您需要查找两个日期之间的项目时，问题就出现了，因为它会过滤掉每天不在这两个日期之间的任何内容。我知道有一种方法可以创建时间戳，但我正在努力使用两个整数将其放在查询内。

我有什么样的选择来解决这个问题?

Answer 1

您可以使用简单的算术:

select date('1900-12-31') + col days

然而，我怀疑真正的日期开始日期是18999-12-31。这是Excel使用的日期(所以第一天是1900-01-01)。

编辑:

哎呀，漏了秒。一种方法是:

select timestamp(date('1900-12-31') + coldays days) +
       (floor(coltime / 10000) * 60*60 +
        mod(floor(coltime / 100)) * 60
        mod(coltime, 100)
       ) seconds

Answer 2

当你有一个工作的答案(从你的评论)…

select  * from mytable
where CURRENT_TIMESTAMP 
         < timestamp(date('1900-12-31') + coldays days) 
              + (floor(coltime / 10000) * 60*60 
              + mod(floor(coltime / 100),100) * 60 mod(coltime, 100) ) 

您可能会发现它执行得不是很好，因为WHERE子句中对列的函数使用限制了标准索引的可用性。在最好的情况下，您将获得一个完整的索引扫描，在最坏的情况下，您将获得一个完整的表扫描。

两个选项1)最新版本的操作系统(v6.1及更高版本)可以创建派生索引。

create index mytsidx on mytable 
  (timestamp(date('1900-12-31') + coldays days) 
             + (floor(coltime / 10000) * 60*60 
             + mod(floor(coltime / 100),100) * 60 mod(coltime, 100) )     
                seconds )

2)在较旧的版本中，您可能希望将传入的时间戳转换为部分并进行如下比较:

   select * from mytable
     where (strdays < coldays 
             or strdays = coldays and strtime <= coltime)
        and (enddays > coldays
              or enddays = coldays and endtime >= coltime)

如果选择选项1，我建议创建一个UDF来处理转换。

   create index mytsidx on mytable (CONVERT_TO_TS(coldays,coltime));
   select * from mytable
     where CONVERT_TO_TS(coldays,coltime)
              between start_ts and end_ts;