GROUP BY time interval of X days, from a specific date

Question

I'm trying to figure out if it's possible to group data by a time interval of X days, starting from a specific date. I know how to group it by days, weeks, months, etc., but I'm stumped for arbitrary time intervals.

For instance ... given a SQL table with timestamp and session_count columns, I'd like to generate aggregations grouped by a time interval (in this case, 30 days) from a specific date (in this case, today).

So, if today is 2015-08-31, and I want to look back over the past 90 days, then I would expect to get three results, each covering a 30-day period:

+-------------------+---------------+
| start_of_interval | session_count |
+-------------------+---------------+
| 2015-08-01        |       1234567 |
| 2015-07-02        |       2345678 |
| 2015-06-02        |       3456789 |
+-------------------+---------------+

Ideally, I'd like to be able to specify an arbitrary number of days to use as the interval, and an arbitrary date to work back from.

EDIT:

This is the general approach I expect to need to use:

SELECT ROUND(CEILING(UNIX_TIMESTAMP(timestamp) / 2592000) * 2592000) AS thirtydays, 
SUM(session_count) AS session_count_sum
FROM my_table
GROUP BY thirtydays

But I'm not sure how to specify the start date from which we should be working back from in the thirtydays portion of the query.

you know that it is not a free code writing community, right? — Iłya Bursov
Yes, I know. I don't want anybody to write my code for me. But pointing me in the right direction would be nice. The basic question is: Is it possible to group by a time interval of X days, starting from a given date? I'm sorry if the way I worded the question came off as asking other people to do my work for me. I'll try to revise. — jawns317
yes, it is possible, read about group by and mysql date functions — Iłya Bursov
I'm familiar with both of them. Frankly, if you think that hint is all I need, I'm not sure you fully understand my question. — jawns317

jawns317 jawns317 · Accepted Answer · 2015-08-31T18:12:14

This aggregates it in 30-day chunks:

SELECT 

FLOOR(TIMESTAMPDIFF(
            DAY, timestamp, '2015-08-31 00:00:00'
) / 30) AS thirtydays, 

SUM(session_count)
FROM my_table
GROUP BY thirtydays;

And this returns it with the start date of the interval:

SELECT DATE_SUB('2015-08-31 00:00:00', INTERVAL
    CEILING(
        TIMESTAMPDIFF(DAY, timestamp, '2015-08-31 00:00:00') / 30
    ) * 30
DAY) AS thirtydays, 

SUM(session_count)
FROM my_table
GROUP BY thirtydays;

GROUP BY time interval of X days, from a specific date

1 Answers