MySQL组通过自定义时间戳 [英] MySQL Group By custom timestamp

问题描述

我想从按日期分组的数据库中获得结果。由此产生的消息应按日期从上午5点到上午5点进行分组。换句话说，截止时间应该是凌晨5点，而不是上午12点。我可以在正常的一天进行 GROUP BY DAY（timestamp）分组，但如果我必须改变时间并且每个记录应该从早上5点到上午5点分组，那该怎么办？我应该如何改变查询？谢谢？

I want to get results from the database which is grouped by date. The resulting touples should be grouped by date which ranges from 5 am to 5 am. In other words the cutoff should be on 5 am and not midnight 12 am. I can do GROUP BY DAY(timestamp) to group by normal day but what if I have to vary the time and each records should be grouped from 5 am to 5 am ? How should I change the query ? Thanks ?

推荐答案

只需从每个日期时间值中减去5小时，然后将其转换为日期。

Simply subtract 5 hours from each datetime value you have, and then convert it to a date. Then you can group by the result.

GROUP BY DATE(DATE_SUB(timestamp, INTERVAL 5 HOUR))

这篇关于MySQL组通过自定义时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！