MySQL查询按日期范围分组? [英] MySQL Query to group by date range?

问题描述

我在MySQL中有一张表，该表向我显示了每天记录的小时数.我正在尝试建立一个视图，该视图将允许我按块/天范围快速将数据分组.最简单的情况是每月一次，这并不难.我可以将日期选择为％y-％m"，然后按该列分组.

I've got a table in MySQL that shows me number of hours logged on a daily basis. I'm trying to build a view that will allow me to quickly group my data by blocks/range of days. The simplest case would be on a monthly basis which wouldn't be difficult. I could just select the date as "%y-%m" and then group by that column.

例如:

select time_logged, date_format(start_date, '%Y-%m') AS `month_logged`
from work_log
group by month_logged

如果我按月分组，那很好.但是我的问题是，我需要从当月的13号到下个月的12号进行分组(例如:7月13日至8月12日，8月13日至9月12日，等等).

That works fine if I am just grouping by month. But my issue is that I need to group from the 13th of the month to the 12th of the following month (ex: July 13-Aug 12, Aug 13- Sept 12, etc).

是否有一种简单的方法可以在单个查询/视图中执行类似的操作?我似乎无法提出适合我需要的查询，即使使用不同的日期字段组合也是如此.

Is there an easy way to do something like that in a single query/view? I can't seem to come up with a query that works for my needs, even playing with the different date field combinations.

推荐答案

减去13天并进行您现在要进行的分组:

Subtract 13 days and do the grouping you are doing now:

select time_logged,
       date_format(start_date - interval 12 day, '%Y-%m') AS `month_logged`
from work_log
group by month_logged;

这篇关于MySQL查询按日期范围分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！