MySQL: How to calculate weeks out from a specific date?

Question

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

Iraq

What do you want to do for partial weeks? For example: should 3.2857 weeks be 3 weeks, 4 weeks or something in between? – Juggins

Yes, it should be rounded to the nearest whole week – Iraq

Answer 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.

Gangling

Plus one But can you add the link to the documentation for better explanations . Why the 0 after the 7 ? – Glomerulonephritis

@JohnMax : Added info about the 0. – Zinovievsk

Answer 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

Demott

Answer 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)

Tinaret

Nice explanation +1 from my side – Hallerson

your (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. – Laveta

Answer 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;

Laveta

Answer 5

Try TIMESTAMPDIFF

TIMESTAMPDIFF(WEEK, curdate(), EventDate) AS WEEKS_DIFFERENCE

Swarm