将 SQL 数字转换为时间，以执行日期比较

发布于 2024-09-19 17:12:02 字数 478 浏览 7 评论 0原文

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

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

一些示例数据可能是

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

如果我简单地减去这两个值，我会得到 4043 秒，而交易时间仅为 3。然而，我正在努力将这些值转换为允许我执行日期比较的时间格式。

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

编辑：

澄清

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

更糟糕的是，午夜过后 1 分钟 0:01:00 将表示为 100。

原文

I am struggling with an SQL time comparison on two fields that have been set up as integers, rather than time stamps.

I have some performance metrics in a database that the developer originally set up as int(8). The database contains the start and end times of a transaction. For example

Some sample data may be

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

If I simply subtracted the two values, I would get 4043 seconds, when the transaction time is only 3. I am struggling however to convert the values into a time format that allows me to perform date comparisons.

I am unable to do this calculation within the application, because there are 100's of rows in the database per day, and I am trying to calculate the average and max times for the transactions.

EDIT:

To clarify

The times are in seconds
85958 represent 8:59:58 90001 represents 9:00:01

To make things worse, 1 minute past midnight 0:01:00 would be represented as 100.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

千寻… 2024-09-26 17:12:02

我在 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

I tested this in MySQL but I'm sure the technique can be adapted to work in 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

Result:

The way it works is to use integer division and the modulo operation to extract the HH MM and SS parts of each timestamp and convert each part into seconds. The seconds are then added together to form a total number of seconds since midnight for each timestamp. The difference between these two gives the transaction time.

Note that this won't work if the transaction starts before midnight and finishes after midnight. You may need to consider if the day has changed and correct for this. If you don't have the day stored in your database then you can look for negative transation times and add 24 hours to make them positive and this should give the correct result (as long as transactions don't exceed one day in length, but I guess that this is unlikely in practice).

My attempt at writing this for DB2 (not tested):

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

回复收藏 0 原文

穿越时光隧道 2024-09-26 17:12:02

假设您使用 DB2 for LUW，您可以使用以下几个函数来完成此操作：

DIGITS() – 为您提供整数的零填充字符串
TRANSLATE() – 重新格式化字符串
MIDNIGHT_SECONDS – 返回自午夜以来的秒数一时间。

这在值 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;

如果 start_time > 则上述表达式将不起作用。 end_time（即start_time 在午夜之前，但end_time 在午夜之后）。

当然，这里真正的问题是使用 INT 来存储 TIME。最好只是修复您的数据模型，以便它使用 TIME（或者更好，时间戳）。

Assuming you're using DB2 for LUW, you can do this using a few functions:

DIGITS() – gives you a zero-padded character string of your integer
TRANSLATE() - reformat the character string
MIDNIGHT_SECONDS - return the number of seconds since midnight for a time.

This will work in cases were the value 100 = '00:01:00'.

Example:

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;

The above expression will not work if start_time > end_time (i.e., start_time is before midnight, but end_time is after midnight).

Of course, the real problem here is using an INT to store a TIME. It would be better to just fix your data model so it uses TIME (or better, a TIMESTAMP).

回复收藏 0 原文

悲念泪 2024-09-26 17:12:02

您确定需要转换吗？也许该数字代表毫秒，因此差异约为 4 秒。

回复收藏 0 原文

蹲墙角沉默 2024-09-26 17:12:02

我建议通过猜测最终差异为 3，这些值实际上应该是 08:59:58 和 09:00:01（以小时：分钟：秒计），

但这些都是猜测，您需要询问公司中的其他人它们是什么- 因为即使原始编码员已经离开，其他人也一定使用了这些

回复收藏 0 原文

负佳期 2024-09-26 17:12:02

已经描述的大多数答案都是有效的，但不幸的是 iSeries 似乎对不同的功能有争议，因此我必须调整给出的答案才能使其正常工作。

我得到的最终解决方案是

选择时间(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)) 从表1；

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

回复收藏 0 原文

~没有更多了~