php - 如何选择给定日期期间的最短开始时间和最长结束时间之间的小时差？

Question

我遇到了一个简单的问题，但我很难尝试制定正确的 SQL 查询来完成此任务。我有一个有多个日期的表。每个日期都有多次。时间是用户手动添加的开始 - 结束增量。

+----+------------------+------------------+
| id | start            | end              |
+----+------------------+------------------+
|  6 | 2014/03/03 08:30 | 2014/03/03 09:00 |
|  7 | 2014/03/03 09:00 | 2014/03/03 11:15 |
|  8 | 2014/03/03 11:15 | 2014/03/03 11:45 |
|  9 | 2014/03/03 11:45 | 2014/03/03 12:45 |
| 10 | 2014/03/03 12:45 | 2014/03/03 13:15 |
| 11 | 2014/03/04 08:45 | 2014/03/04 09:00 |
| 12 | 2014/03/04 09:00 | 2014/03/04 13:00 |
| 13 | 2014/03/04 13:00 | 2014/03/04 13:30 |
| 14 | 2014/03/05 09:00 | 2014/03/05 09:30 |
| 15 | 2014/03/05 09:30 | 2014/03/05 12:30 |
| 16 | 2014/03/05 12:45 | 2014/03/05 12:45 |
| 19 | 2014/03/06 08:45 | 2014/03/06 09:00 |
| 20 | 2014/03/06 09:15 | 2014/03/06 10:00 |

我正在尝试计算每个日期最早开始时间和最晚结束时间之间的小时差。

目前我只是在我的行上使用一个简单的 unix_timestamp 转换，但是，它只计算找到的第一行之间的差异，而不是给定日期所有行的差异。

select
    unix_timestamp(min(start)) - unix_timestamp(max(end)) / 60.0 / 60.0 as hours_difference,
    min(start) as started,
    min(end) as ended              
from
    athlete_log
group by
    day(start)

问题(如上所述)是它只抓取给定日期的第一行，例如:

| 6 | 2014/03/03 08:30 | 2014/03/03 09:00 |

但我需要最早开始时间和最晚开始时间:

|  6 | 2014/03/03 08:30 | 2014/03/03 09:00 |
|  7 | 2014/03/03 09:00 | 2014/03/03 11:15 |
|  8 | 2014/03/03 11:15 | 2014/03/03 11:45 |
|  9 | 2014/03/03 11:45 | 2014/03/03 12:45 |
| 10 | 2014/03/03 12:45 | 2014/03/03 13:15 |

应该产生:

2014/03/03 8:30 - start
2014/03/03 13:15 - end
5.45 - hours_difference

但当然只是选择

2014/03/03 8:30 - start
2014/03/03 9:00 - end
.30 - hours_difference

如何修改我的查询以选择给定日期的最小开始时间和最大结束时间？

Answer 1

您在 DAY() 上分组，这可能是不正确的。您只在那里显示 2014 年 3 月的数据，但如果您的数据库实际上有比这更多的月份/年份的数据，您将分组到 2014/03/03、2014/02/03、2013/02/03 等..(不同的年/月，同一天)

您应该执行 GROUP BY DATE(start)，这样您就可以根据完整的 YYYY-MM-DD 日期值进行分组。