Which mode of MySQL WEEK() complies with ISO 8601 and matches PHP’s DATE function

To mix MySQL week numbers with PHP, can be a bit confusing. The best way to get a relevant match is to use mode 3 in the MySQL WEEK() function.

For example, in PHP:
date(‘W’, strtotime(‘2019-12-31’));
Returns: 01

date(‘W’, strtotime(‘2020-12-31’));
Returns: 53

date(‘W’, strtotime(‘2021-12-31’));
Returns: 52

The same in MySQL:
SELECT WEEK(‘2019-12-31’, 3);
Returns: 1

SELECT WEEK(‘2020-12-31’, 3);
Returns: 53

SELECT WEEK(‘2021-12-31’, 3);
Returns: 52

When mixing up the data in PHP, you might have to at a leading zero to weeks 1 to 9 to match the MySQL week numbers with PHP’s week numbers.

Source: StackOverflow

By Kathir Vel

An agile ebusiness & marketing consultant based in Edinburgh, Scotland, UK. ICT specialist with a MBA in marketing and strategy. Seasoned project manager with strong roots in advertising campaigns and film making. Loves photography and travelling.

Leave a Reply

Your email address will not be published. Required fields are marked *