Mysql TIMESTAMPDIFF for time datatype返回负值

Question

以下是我的问题

SELECT r.*,TIMESTAMPDIFF(MINUTE,r.from, r.to) / 60 as hours from RestaurantOpeningHours r;

结果如下：

id     rId day  from        to          createdAt           updatedAt           hours
142054  1   1   11:00:00    14:00:00    2020-09-07 19:52:30 2020-09-07 19:52:30 3.0000
142055  1   2   13:15:00    03:15:00    2020-09-07 19:52:30 2020-09-07 19:52:30 -10.0000

预期结果：

id     rId day  from        to          createdAt           updatedAt           hours
142054  1   1   11:00:00    14:00:00    2020-09-07 19:52:30 2020-09-07 19:52:30 3.0000
142055  1   2   13:15:00    03:15:00    2020-09-07 19:52:30 2020-09-07 19:52:30 14.0000

因为从13:15:00到03:15:00有14个小时的差异。始终假定to字段上的值是将来的值。from和to字段为time数据类型

哪个正确的查询将返回我预期的结果？

Answer 1

您的模式是什么以及day字段的含义并不是很清楚。但是要得到你想要的东西，你只需要考虑到day：

SELECT
    r.*, TIMESTAMPDIFF(HOUR, r.from, r.to  + INTERVAL (CASE WHEN r.to < r.from THEN 1 ELSE 0 END) DAY) AS hours
FROM
    RestaurantOpeningHours r
;

它给出了你想要的：

id      rId day from        to          createdAt               updatedAt               hours
142054  1   1   11:00:00    14:00:00    2020-09-07T19:52:30Z    2020-09-07T19:52:30Z    3
142055  1   2   13:15:00    03:15:00    2020-09-07T19:52:30Z    2020-09-07T19:52:30Z    14

通过更改TIMESTAMPDIFF的第一个参数并删除除法60，我更改了hours的计算方式，并从分钟中删除了获取小时数。

如果你已经提供了一些创建和填充脚本，那就更好了。

以下是我的观点：

CREATE TABLE RestaurantOpeningHours(
  `id` INT PRIMARY KEY,
  `rId` INT,
  `day` INT,
  `from` TIME,
  `to` TIME,
  `createdAt` TIMESTAMP,
  `updatedAt` TIMESTAMP
);

INSERT INTO RestaurantOpeningHours VALUES
  (142054, 1, 1, "11:00:00", "14:00:00", "2020-09-07 19:52:30", "2020-09-07 19:52:30"),
  (142055, 1, 2, "13:15:00", "03:15:00", "2020-09-07 19:52:30", "2020-09-07 19:52:30")
;

Answer 2

Mysql TIMESTAMPDIFF函数是这样工作的。

TIMESTAMPDIFF(单位，datetime_expr1，datetime_expr2)；

​

什么是区间值？MySQL间隔值主要用于日期和时间计算。要创建间隔值，请使用以下表达式：

(INTERVAL expr单位) ex:间隔1天

最后，我使用了if函数。

我希望这段代码可以帮助你解决你的问题。

SELECT
    r.*, TIMESTAMPDIFF(HOUR, r.from, r.to  + Interval if(r.from<r.to,0,1) day) AS hours
FROM
    RestaurantOpeningHours r
;