Return the count of rows grouped by week MySQL

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.

Tags:

Mysql

Database

Sql

Php

Related