How to group by week in MySQL

Question

Oracle s table server offers a built-in function  TRUNC timestamp  DY    This function converts any timestamp to midnight on the previous Sunday  What s the best way to do this in MySQL   Oracle also offers TRUNC timestamp  MM   to convert a timestamp to midnight on the first day of the month in which it occurs  In MySQL  this one is straightforward   TIMESTAMP DATE FORMAT timestamp    Y- m-01      But this DATE FORMAT trick won t work for weeks  I m aware of the WEEK timestamp  function  but I really don t want week number within the year  this stuff is for multiyear work

User · Answer

You can get the concatenated year and week number (200945) using the YEARWEEK() function. If I understand your goal correctly, that should enable you to group your multi-year data.

If you need the actual timestamp for the start of the week, it's less nice:

DATE_SUB( field, INTERVAL DAYOFWEEK( field ) - 1 DAY )

For monthly ordering, you might consider the LAST_DAY() function - sort would be by last day of the month, but that should be equivalent to sorting by first day of the month ... shouldn't it?

User · Answer

Just ad this in the select    DATE FORMAT  yourDate     X  V    as week   And   group by week

User · Answer

If you need the  week ending  date this will work as well   This will count the number of records for each week   Example   If three work orders were created between  inclusive  1 2 2010 and 1 8 2010 and 5 were created between  inclusive  1 9 2010 and 1 16 2010 this would return   3  1 8 2010 5  1 16 2010  I had to use the extra DATE   function to truncate my datetime field   SELECT COUNT     DATE ADD  DATE wo date created   INTERVAL  7 - DAYOFWEEK  wo date created    DAY  week ending FROM work order wo GROUP BY week ending

User · Answer

Figured it out    it s a little cumbersome  but here it is   FROM DAYS TO DAYS TIMESTAMP  -MOD TO DAYS TIMESTAMP  -1  7     And  if your business rules say your weeks start on Mondays  change the -1 to -2     Edit  Years have gone by and I ve finally gotten around to writing this up  http   www plumislandmedia net mysql sql-reporting-time-intervals

User · Answer

The accepted answer above did not work for me  because it ordered the weeks by alphabetical order  not chronological order   2012 1 2012 10 2012 11     2012 19 2012 2   Here s my solution to count and group by week   SELECT CONCAT YEAR date        WEEK date   AS week name          YEAR date   WEEK date   COUNT    FROM column name GROUP BY week name ORDER BY YEAR DATE  ASC  WEEK date  ASC   Generates   YEAR WEEK   YEAR   WEEK   COUNT 2011 51     2011    51      15 2011 52     2011    52      14 2012 1      2012    1       20 2012 2      2012    2       14 2012 3      2012    3       19 2012 4      2012    4       19

User · Answer

You can use both YEAR timestamp  and WEEK timestamp    and use both of the these expressions in the SELECT and the GROUP BY clause   Not overly elegant  but functional     And of course you can combine these two date parts in a single expression as well  i e  something like  SELECT CONCAT YEAR timestamp        WEEK timestamp    etc    FROM     WHERE    GROUP BY CONCAT YEAR timestamp        WEEK timestamp       Edit  As Martin points out you can also use the YEARWEEK mysqldatefield  function  although its output is not as eye friendly as the longer formula above     Edit 2  3 1 2 years later     YEARWEEK mysqldatefield  with the optional second argument  mode  set to either 0 or 2 is probably the best way to aggregate by complete weeks  i e  including for weeks which straddle over January 1st   if that is what is desired  The YEAR     WEEK   approach initially proposed in this answer has the effect of splitting the aggregated data for such  straddling  weeks in two  one with the former year  one with the new year  A clean-cut every year  at the cost of having up to two partial weeks  one at either end  is often desired in accounting etc   and for that the YEAR     WEEK   approach is better

[mysql] How to group by week in MySQL?

Examples related to mysql

Examples related to datetime

Examples related to group-by

Examples related to data-migration

Examples related to dayofweek