Week of year format is different on MySql and PHP

Question

I'm trying to get the same seek number with PHP (Carbon - laravel) and MySql using the same date. At the end of the year, PHP returns week 53 and Mysql, using the same date, returns 52 but both starts with week number 1, how can this be possible?

Here is an example:

Same date, different output:

PHP:

$phpDate = Carbon::create('2020','12','31');
dd($phpDate->format('W'));
//output - 53

MySql

select DATE_FORMAT("2020-12-31", '%V') from aRandomTable
//output 52

And then i questioned, maybe, MySQL is starting on Zero, but then i made another try:

PHP:

$phpDate = Carbon::create('2021','01','05');
dd($phpDate->format('W'));
//output - 01

MySQL

select DATE_FORMAT("2021-01-05", '%V') from aRandomTable
//output - 01

And both start with week 1.

Why this happens? How can i fix this?

Answer 1

The reason they get different is because in the PHP date format, when you use W you get The ISO-8601 week number of year (weeks starting on Monday) and in mysql, to get the same week number (with weeks starting mondays) you need v (lower case). If you use V (upper case) you are getting weeks starting on Sunday

Use SELECT WEEKOFYEAR('2020-12-31') on mysql and you will get 53 or use select DATE_FORMAT("2020-12-31", '%v') with the v in lower case and you will get 53

Answer 2

To understand where the difference comes from, we need to take a look at how these functions calculate the week.

The MySQL DATE_FORMAT docs say the following about %V:

Week (01..53), where Sunday is the first day of the week

The PHP DateTime::format docs say the following about W:

ISO-8601 week number of year, weeks starting on Monday

So you can see that they don't use the same starting day. To amend this, you need to change your SQL to either use %v (lowercase) as the format or the WEEKOFYEAR function.