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

What do you want to do for partial weeks? For example: should 3.2857 weeks be 3 weeks, 4 weeks or something in between? — Abe Miessler, Oct 18 '10 at 15:01

score 21 · Accepted Answer · edited Sep 12 '17 at 17:08

21

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.

edited Sep 12 '17 at 17:08

Peter K.

8,028
4
48
73

answered Oct 18 '10 at 15:05

OMG Ponies

325,700
82
523
502

Plus one But can you add the link to the documentation for better explanations . Why the 0 after the 7 ? – John Max Mar 19 '16 at 15:38
2

@JohnMax : Added info about the 0. – Peter K. Sep 12 '17 at 17:08

score 3 · Answer 2 · answered Nov 03 '14 at 17:18

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

score 2 · Answer 3 · answered Oct 18 '10 at 15:07

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)

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. — Ludo - Off the record, Jun 09 '20 at 08:48

score 1 · Answer 4 · answered Jun 09 '20 at 08:57

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;

score 0 · Answer 5 · answered Sep 18 '22 at 23:12

0

Try TIMESTAMPDIFF

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

answered Sep 18 '22 at 23:12

Sagnik

1,446
1
9
13

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

5 Answers5

Linked