CODEWITHSUNDEEP
mysqlsql
jawns317
jawns317

Reputation: 1746

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

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.

Upvotes: 0

Views: 2231

Answers (1)

jawns317
jawns317

Reputation: 1746

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;

Upvotes: 2

Related Questions