115

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172

9 Answers9

148

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.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
mjv
  • 73,152
  • 14
  • 113
  • 156
  • 16
    `YEARWEEK()` results in an `INT(6)` which I believe will be faster to sort/join/group with – KCD Jun 05 '12 at 22:44
  • 2
    @mjv: what happens when two year shares the same week ? like for this 31-12-2012 To 6-1-2013 (Mon to Sun). is there any solution to this ? – hardik Aug 17 '13 at 12:10
  • 1
    @hardik: see Edit 2. Depending on one's needs YEARWEEK() may be a better key to aggregate on, if one would rather work with complete weeks rather than splitting the last/first partial week of the year. – mjv Aug 17 '13 at 18:50
  • @mjv here's another solution for that [link](http://stackoverflow.com/questions/18288446/group-by-week-in-mysql/18288543#18288543) – hardik Aug 21 '13 at 07:46
  • make sure the "timestamp" is not an integer, like i had in my table. WEEK(timestamp) takes date/timestamp column type as a valid argument – Usman Shaukat Dec 24 '15 at 19:54
  • 1
    To display the date value, you can convert it back to the first day of the week: `select str_to_date(concat(yearweek('2019-01-01',2),'0'), '%X%V%w');` – Sam Barnum Sep 23 '19 at 18:36
  • `WEEK(timestamp)` is not valid. MySQL emits a warning: `Incorrect datetime value: '1638286179'` – Sebi2020 Nov 30 '21 at 15:31
  • if December 31 is Tuesday, this Tuesday and Wednesday belong to separate week, but it should be in same week. so this is not 100% correct solution. – Abdullah Nurum Aug 24 '23 at 15:09
56

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. https://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/

O. Jones
  • 103,626
  • 17
  • 118
  • 172
43

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
B Seven
  • 44,484
  • 66
  • 240
  • 385
  • 6
    ordering by date (`ORDER BY date ASC`) should do the trick too – Fender May 31 '16 at 12:12
  • @Fender You would not be able to do "date" as the ORDER if the table is set to only allow strict columns for group by (date is not in the group by YEAR and WEEK version are), which is now the default on MYSQL – tristanbailey Feb 19 '21 at 22:39
27

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?

martin clayton
  • 76,436
  • 32
  • 213
  • 198
4

Just ad this in the select :

DATE_FORMAT($yourDate, \'%X %V\') as week

And 

group_by(week);
Xavier
  • 57
  • 1
3

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;
Deepak Mahakale
  • 22,834
  • 10
  • 68
  • 88
Randy
  • 1,498
  • 13
  • 8
3

Previous Sunday:

STR_TO_DATE(CONCAT(YEARWEEK(timestamp,2),'0'),'%X%V%w')

Previous Monday:

STR_TO_DATE(CONCAT(YEARWEEK(timestamp,3),'1'),'%x%v%w')

DATE_FORMAT(date,format) reference:

  • %V - Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
  • %v - Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
  • %w - Day of the week (0=Sunday..6=Saturday)
  • %X - Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
  • %x - Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
Jon-Eric
  • 16,977
  • 9
  • 65
  • 97
1

I like the week function in MySQL, but in my situation, I wanted to know which week of the month a row was in. I utlized this solution:

where run_date is a timestamp like 2021-02-25 00:00:00

concat ( 
        date_format(run_date, '%Y-%m'), 
        ' wk ', 
        (week(run_date,1) - ( week(date_format(run_date, '%Y-%m-01')) - 1))
        ) as formatted_date

This outputs:

2021-02-23 --->    2021-02 wk 4
2021-02-25 --->    2021-02 wk 4
2021-02-11 --->    2021-02 wk 2
2021-03-02 --->    2021-03 wk 1

The idea behind this is that I want to know (with relative certainty) which week of the month in question did the date occur?

So we concatenate:

date_format(run_date, '%Y-%m') to get 2021-02

then we add the literal text string wk

then we use: week(run_date, 1) to get the week (1 to start Monday) of this record, (which would be 7 because 02/21/2021 is in the 7th week of the year, and we subtract whatever the week is on the 1st day of this same month - the week() for 2021-02-01 is 5, because it is in the 5th week of the year:

(week(date_format(run_date, '%Y-%m-01'))

Unfortunately, this will start out the counting at 0, which people don't like, so we subtract 1 from the last part of the concatenation result so that the "week" start at 1.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Brian Powell
  • 3,336
  • 4
  • 34
  • 60
1

This may be a good option:

SELECT
 year(datetime_field) as year_date, week(datetime_field) as week_date
FROM
 bd.table
GROUP BY
 year_date, week_date;

It would look like this:

  • '2020', '14'
  • '2020', '15'
  • '2020', '16'
  • '2020', '17'
  • '2020', '18'
Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
Sebastian Romero Laguna
  • 13
  • 2