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?

question from:https://stackoverflow.com/questions/65939538/week-of-year-format-is-different-on-mysql-and-php

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