代码之家 › 专栏 › 技术社区 › Codemwnci

将SQL编号转换为时间,以执行日期比较

ibm-midrange db2 comparison datetime sql

Codemwnci · 技术社区 · 14 年前

我正在努力对两个字段进行SQL时间比较,这些字段被设置为整数,而不是时间戳。

我在开发人员最初设置为int(8)的数据库中有一些性能指标。数据库包含事务的开始和结束时间。例如

一些样本数据可能

id | start_time | end_time
---------------------------
1  |      85958 | 90001

如果我简单地减去这两个值,当事务时间只有3秒时,我会得到4043秒。但是,我很难将这些值转换为允许我执行日期比较的时间格式。

我无法在应用程序中进行这种计算,因为数据库中每天有100行,我正在尝试计算事务的平均时间和最大时间。

编辑:

澄清

时间以秒为单位 85958代表8:59:58 90001代表9:00:01

更糟的是,超过午夜0:01:00的1分钟将被表示为100。

5 回复 | 直到 13 年前

Mark Byers 14 年前

我在MySQL中对此进行了测试,但我确信该技术可以在DB2中使用:

SELECT
    (end_time DIV 10000) * 3600 +
    ((end_time DIV 100) % 100) * 60 +
    end_time % 100 -
    (start_time DIV 10000) * 3600 -
    ((start_time DIV 100) % 100) * 60 -
    start_time % 100
FROM table1

结果:

它的工作方式是使用整数除法和模运算来提取每个时间戳的hh-mm和ss部分,并将每个部分转换为秒。然后将秒相加,形成每个时间戳从午夜开始的总秒数。这两者之间的差异给出了事务时间。

请注意,如果事务在午夜之前开始,而在午夜之后结束,则此操作将不起作用。您可能需要考虑当天是否发生了变化,并对此进行纠正。如果您的数据库中没有存储日期,那么您可以查找负的转换时间,并添加24小时使其成为正的,这将给出正确的结果(只要事务长度不超过一天,但我想在实践中这是不可能的)。

我尝试为DB2编写这个(未测试):

SELECT
    (end_time / 10000) * 3600 +
    MOD(end_time / 100, 100) * 60 +
    MOD(end_time, 100) -
    (start_time / 10000) * 3600 -
    MOD(start_time / 100, 100) * 60 -
    MOD(start_time, 100)
FROM table1

Ian Bjorhovde 14 年前

假设您使用的是DB2forluw,那么您可以使用以下几个函数来实现这一点:

digits()给您一个零填充的整数字符串。
translate()-重新格式化字符串
午夜-返回从午夜开始的一段时间的秒数。

如果值为100='00:01:00'。

例子:

select
   id,
   MIDNIGHT_SECONDS(TRANSLATE('EF:GH:IJ',DIGITS(end_time),'ABCDEFGHIJ')) -
      MIDNIGHT_SECONDS(TRANSLATE('EF:GH:IJ',DIGITS(start_time),'ABCDEFGHIJ')) as runtime
from
   your_table;

如果开始时间>结束时间(即开始时间在午夜之前,但结束时间在午夜之后),则上述表达式将不起作用。

当然,这里真正的问题是使用int来存储时间。最好只是修复您的数据模型,以便使用时间(或者更好的是时间戳)。

D'Arcy Rittich 14 年前

您确定需要转换吗?也许这个数字代表毫秒,所以相差大约4秒。

mmmmmm 14 年前

我建议值实际上应该是08:59:58和09:00:01(小时:分钟:秒),通过猜测结束差异为3

但这些都是您需要询问公司中其他人的猜测,因为即使原始编码人员已经离开,其他人也必须使用这些猜测。

Codemwnci 14 年前

已经描述过的大多数答案都是有效的,但不幸的是,ISeries似乎在不同的功能上争论不休,所以我必须调整给出的答案,以使其发挥作用。

我得到的最终解决方案是

select TIME(SUBSTR(DIGITS(END_TIME),1,2) CONCAT ':' CONCAT SUBSTR(DIGITS(END_TIME),3,2) CONCAT ':' CONCAT SUBSTR(DIGITS(END_TIME),5,2)) - TIME(SUBSTR(DIGITS(START_TIME),1,2) CONCAT ':' CONCAT SUBSTR(DIGITS(START_TIME),3,2) CONCAT ':' CONCAT SUBSTR(DIGITS(START_TIME),5,2)) from table1;

感谢您快速而详细的回复。