Return the count of rows grouped by week MySQL

Question

I am trying to get the row count from a MySQL table where the data is grouped by WEEK.

So far the query I have is:

 "SELECT count(*) as tweets, twitTimeExtracted as date 
  FROM scene.twitData 
  group by week(twitTimeExtracted)"

This query returns the data below:

As you can see, the weeks are not correct, I'm expecting data for each week starting with Monday 7th January (7,14,21,28,4,11 etc...) and running through to this week.

I also tried a modified version of the orignal query:

SELECT count(*) as tweets, twitTimeExtracted as date 
FROM scene.twitData 
WHERE date(twitTimeExtracted) 
   BETWEEN '2013-01-07' and '2013-03-11' 
group by week(twitTimeExtracted)

This returns similar results as the first query.

Maybe there is an inconsistency with some data stored in the DATETIME: twitTimeExtracted column on a few rows of data? I don't really know I'm not very experienced with MySQL.

Any help would really be appreciated.

Thanks

Answer 1

This converts the datetime value to the appropriate Monday of the week

select count(*) as tweets,
       str_to_date(concat(yearweek(twitTimeExtracted), ' monday'), '%X%V %W') as `date`
from twitData 
group by yearweek(twitTimeExtracted)

yearweek returns both week and year. Together with the string monday, str_to_date gives you the Monday's datetime value.

If your week starts on Monday, use yearweek(twitTimeExtracted, 1) instead.