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:
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.
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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 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.
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
Years have gone by and I’ve finally gotten around to writing this up.
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
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
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?
Just ad this in the select :
DATE_FORMAT($yourDate, '%X %V') as week
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:
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;
%V– Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with
%v– Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with
%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
%x– Year for the week, where Monday is the first day of the week, numeric, four digits; used with
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:
run_date is a timestamp like
concat ( date_format(run_date, '%Y-%m'), ' wk ', (week(run_date,1) - ( week(date_format(run_date, '%Y-%m-01')) - 1)) ) as formatted_date
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
then we add the literal text string
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
2021-02-01 is 5, because it is in the 5th week of the year:
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.
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’