由两个不同的日期和时间组成的mysql。

I need to generate a report based on date and time.

我需要根据日期和时间生成报告。

My database table has payments recorded with amount and created datetime.

我的数据库表中有记录的金额，并创建了datetime。

And, I need to group data like sum of payments from 9:30pm yesterday to 9:29pm today

而且，我需要把昨天晚上9点半到今天晚上9点30分的付款数据进行分组。

(i.e) instead of group by date(created_date). And then records between these time range.

(即)按日期(created_date)代替组。然后记录这些时间范围。

I have the last two months records in my payments table.

在我的支付表中，我有最后两个月的记录。

Any help will be appreciated.

如有任何帮助，我们将不胜感激。

Thanks

谢谢

2 个解决方案

#1

Just add 2.5 hours to each date/time. This is most easily represented at 150 minutes:

只需要增加2.5小时的时间。最容易在150分钟内代表:

group by date(created_date + interval 150 minute)

#2

Thanks for all the answers.

谢谢你的回答。

I ended up with this one - group by 24 hours

我在24小时内完成了这个任务。

SELECT CONCAT( CONCAT(DATE(DATE_SUB(created, INTERVAL '21:30' HOUR_MINUTE)),
' 21:30:00') , ' - ' ,CONCAT(DATE(DATE_ADD(created, INTERVAL '2:30' HOUR_MINUTE)),
'  21:29:59') ) AS tintin, COUNT(*)  FROM test GROUP BY DATE(DATE_SUB(created, 
INTERVAL '21:30' HOUR_MINUTE)) ORDER BY DATE(created) DESC ;

got some idea from here

从这里得到一些想法。

Mysql Group By 24 hour intervals

Mysql组24小时间隔。

#1