时间戳

首页 > 解决方案 > 获取 2 个日期之间时差的统计信息

php - 获取 2 个日期之间时差的统计信息

问题描述

我在 MYSQL 中有 2 个表。第一个包含我的所有预订以及预订日期，第二个包含我所有可用日期时间格式的旅行日期。

这是一个示例：为 2 个不同的旅行进行了 4 次预订。

表：myBooking

ID DATEADD               IDTOUR
1  2020-08-01 13:30:00   1
2  2020-08-02 13:30:00   1
3  2020-08-03 13:30:00   2
4  2020-08-09 13:30:00   2

表：我的旅游

ID  TOUR
1   2020-08-10 13:30:00
2   2020-08-11 13:30:00

我想在 PHP/MYSQL 中概述客户预订的时间间隔。

我想显示：

对于少于 24 小时的预订，旅行日期前少于 1 小时、1 小时至 2 小时等之间的预订总数...

然后，对于超过 24 小时的预订，预订和旅行日期之间的天数。

我发现 DATEDIFF 来获取两个日期之间的时间间隔，但我需要帮助才能完成查询并正确显示结果。

SELECT DATEDIFF(DATEADD, TOUR) as total FROM myBooking JOIN myTours ON myBooking.IDTOUR = myTours.ID

结果应如下所示：

< 1hour : (number of bookings made less than 1hour before the tour)
1-2hours : (number of bookings made between 1-2hours before the tour)
3-4hours : (number of bookings made between 3-4hours before the tour)

等到24小时...

2 days : (number of bookings made 2 days before the tour)
3 days : (number of bookings made 3 days before the tour)

等到30天...

标签： phpmysql

解决方案

我发现 DATEDIFF 来获取两个日期之间的时间间隔，但我需要帮助才能完成查询并正确显示结果。

DATEDIFF 不适用于此任务，因为：

  1. 对于某些行，您需要以小时为单位的差异 - DATEDIFF 仅以天为单位计算差异。
  2. DATEDIFF 在计算前截断时间部分。

例如，解决方案可能是（示意性地）：

SELECT ...
         CASE WHEN TIMESTAMPDIFF(HOUR, booking.date, tour.date) < 24
              THEN CONCAT(TIMESTAMPDIFF(HOUR, booking.date, tour.date), ' hours')
              ELSE CONCAT(TIMESTAMPDIFF(DAY, booking.date, tour.date), ' days')
              END AS IntervalLength
...
GROUP BY IntervalLength
...

这将为每个单独的小时/天提供统计信息。

如果您需要严格描述的统计周期（<1, 1-2, 3-4, ...），则创建单独的表，例如：

CREATE TABLE periods ( hours_from INT, hours_till INT, name INT );
INSERT INTO periods VALUES
(0,0,'<1 hour'),
(1,2,'1-2 hours'),
...
(24,47,'1 day'),
...
(720,743,'30 days'),
(744,99999999,'over 30 days');

通过

ON TIMESTAMPDIFF(HOUR, booking.date, tour.date) 
   BETWEEN periods.hours_from 
       AND periods.hours_till

和分组periods.name。

推荐阅读