Grouping by X days

Question

Hey,

I have a database that shows me different stats about different campaigns, each row has a timestemp value name "date".

I wrote the code for choosing and summerizing a range of dates, for example: 21-24/07/2010. Now I need to add an option to choose a range of dates, but also to group the stats for each X days.

Let's say the user chooses to see stats from all the month: 01/07-31/07. I would like to present him the stats grouped by X days, let's say 3, so he will see the stats 01-03/07, 04-06/07,07-09/07 and so on...

I almost manged doing it using this code:

SELECT t1.camp_id,from_days( floor( to_days( date ) /3 ) *3 ) AS 'first_date'
FROM facebook_stats t1
INNER JOIN facebook_to_campaigns t2 ON t1.camp_id = t2.facebook_camp_id
WHERE date
BETWEEN 20100717000000
AND 20100724235959
GROUP BY from_days( floor( to_days( date ) /3 ) *3 ) , t2.camp_id

It actually do group it (by 3 days), but the problem is that for some reason it starts from the 16/07, and not the 17/07, then grouping each time 3 days at a time.

Would love to hear a solution to the code or I gave, or a better solution you have in mind.

Thanks, Eden

Answer 1

To_Days returns the number of days since the year 0. When you divide it by 3, it considers only the quotient and not the remainder. eg. If it has been 5 days since year 0, then to_days will return 1.

To_days(20100717000000) must be leaving a remainder of 1. Basically To_days(2010071*6*000000) is exactly divisible by 3 but 17th is not.

You could try this query:

DECLARE @startDate datetime
DECLARE @endDate datetime
DECLARE @groupByInterval INT
SET @startdate = 20100717000000
SET @enddate = 20100724235959
SET @groupByInterval = 3

SELECT 
t1.camp_id,   from_days(
to_days(@startDate)+floor(
(to_days(date)-to_days(@startDate))/@groupByInterval
)
* @groupByInterval) 
AS first_date
FROM facebook_stats t1
INNER JOIN facebook_to_campaigns t2 ON t1.camp_id = t2.facebook_camp_id
WHERE date
BETWEEN @startDate
AND @endDate
GROUP BY first_date , t2.camp_id