MySQL: How to calculate weeks out from a specific date?
Solution 1
Use the DATEDIFF function:
ROUND(DATEDIFF(end_date, start_date)/7, 0) AS weeksout
The problem with WEEKS is that it won't return correct results for dates that cross over January 1st.
The 0
is the number of decimal places to use in the ROUND
function.
Solution 2
In order to get past the whole "New Year" issue and you still want to use WEEK()
, I found the following method quite effective.
SELECT
YEAR(end_date)*52+WEEK(end_date)
- YEAR(start_date)*52 - WEEK(start_date) as weeks_out
FROM
events;
The difference with this method (as opposed to the DATEDIFF
method) is that it is aligned with the week. So today (which is Monday) and last Friday would return 1
using this method, but would return 0
with the DATEDIFF
method
Solution 3
Here's a simple way to do it:
SELECT EventDate, (week(EventDate) - week(curdate())) AS WeeksOut FROM Events;
Example:
mysql> select week('2010-11-18') - week ('2010-10-18');
+------------------------------------------+
| week('2010-11-18') - week ('2010-10-18') |
+------------------------------------------+
| 4 |
+------------------------------------------+
1 row in set (0.00 sec)
Another option is calculate the interval in days and divide by 7:
SELECT EventDate, datediff(EventDate,curdate())/7 AS WeeksOut FROM Events;
Example:
mysql> select datediff('2010-11-18' , '2010-10-18') / 7;
+-------------------------------------------+
| datediff('2010-11-18' , '2010-10-18') / 7 |
+-------------------------------------------+
| 4.4286 |
+-------------------------------------------+
1 row in set (0.00 sec)
Solution 4
In newer versions of MYSQL if you use the timestamp as column type, you can use TIMESTAMPDIFF:
SELECT TIMESTAMPDIFF(WEEK, '2020-06-09 08:59:36', '2020-09-09 08:58:25');
|-----------------------------------------------------------------------|
| 13 |
So in your example it would be:
SELECT TIMESTAMPDIFF(WEEK, NOW(), EventDate) AS WeeksOut FROM Events;
Mike
Updated on July 28, 2022Comments
-
Mike almost 2 years
I need to calculate the weeks out from a date in my MySQL select statement. There is a date column in one of the tables, and I need to calculate how many weeks away the date is.
SELECT EventDate, (calculation) AS WeeksOut FROM Events;
Example:
- 6 days away, weeks out = 0
- 7 days away, weeks out = 1
- 13 days away, weeks out = 1
- 14 days away, weeks out = 2
-
w3uiguru about 11 yearsNice explanation +1 from my side
-
John Max about 8 yearsPlus one But can you add the link to the documentation for better explanations . Why the 0 after the 7 ?
-
kidsid49 over 6 years@JohnMax : Added info about the 0.
-
Ludo - Off the record almost 4 yearsyour (first) solution will only work if the dates are within the same year. so for example when the event is in January and it's now December it won't work.