Testing MySQL vs PHP format for matching Week Year formats (Laravel)

conclusion.md

Sometimes you need to select and group by week-year in MySQL but then add missing dates in PHP.

If you want to do this, you are best using the o-W format in PHP and then the %x-%v format in MySQL.

PHP Docs: https://www.php.net/manual/en/datetime.format.php

MySQL Docs: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

result.json

[
  {
    "actualDate": "Saturday, 1 January 2000",
    "mysqlDate": "1999-52",
    "phpDate": "1999-52",
    "datesMatch": true
  },
  {
    "actualDate": "Monday, 1 January 2001",
    "mysqlDate": "2001-01",
    "phpDate": "2001-01",
    "datesMatch": true
  },
  {
    "actualDate": "Tuesday, 1 January 2002",
    "mysqlDate": "2002-01",
    "phpDate": "2002-01",
    "datesMatch": true
  },
  {
    "actualDate": "Wednesday, 1 January 2003",
    "mysqlDate": "2003-01",
    "phpDate": "2003-01",
    "datesMatch": true
  },
  {
    "actualDate": "Thursday, 1 January 2004",
    "mysqlDate": "2004-01",
    "phpDate": "2004-01",
    "datesMatch": true
  },
  {
    "actualDate": "Saturday, 1 January 2005",
    "mysqlDate": "2004-53",
    "phpDate": "2004-53",
    "datesMatch": true
  },
  {
    "actualDate": "Sunday, 1 January 2006",
    "mysqlDate": "2005-52",
    "phpDate": "2005-52",
    "datesMatch": true
  },
  {
    "actualDate": "Monday, 1 January 2007",
    "mysqlDate": "2007-01",
    "phpDate": "2007-01",
    "datesMatch": true
  },
  {
    "actualDate": "Tuesday, 1 January 2008",
    "mysqlDate": "2008-01",
    "phpDate": "2008-01",
    "datesMatch": true
  },
  {
    "actualDate": "Thursday, 1 January 2009",
    "mysqlDate": "2009-01",
    "phpDate": "2009-01",
    "datesMatch": true
  },
  {
    "actualDate": "Friday, 1 January 2010",
    "mysqlDate": "2009-53",
    "phpDate": "2009-53",
    "datesMatch": true
  },
  {
    "actualDate": "Saturday, 1 January 2011",
    "mysqlDate": "2010-52",
    "phpDate": "2010-52",
    "datesMatch": true
  },
  {
    "actualDate": "Sunday, 1 January 2012",
    "mysqlDate": "2011-52",
    "phpDate": "2011-52",
    "datesMatch": true
  },
  {
    "actualDate": "Tuesday, 1 January 2013",
    "mysqlDate": "2013-01",
    "phpDate": "2013-01",
    "datesMatch": true
  },
  {
    "actualDate": "Wednesday, 1 January 2014",
    "mysqlDate": "2014-01",
    "phpDate": "2014-01",
    "datesMatch": true
  },
  {
    "actualDate": "Thursday, 1 January 2015",
    "mysqlDate": "2015-01",
    "phpDate": "2015-01",
    "datesMatch": true
  },
  {
    "actualDate": "Friday, 1 January 2016",
    "mysqlDate": "2015-53",
    "phpDate": "2015-53",
    "datesMatch": true
  },
  {
    "actualDate": "Sunday, 1 January 2017",
    "mysqlDate": "2016-52",
    "phpDate": "2016-52",
    "datesMatch": true
  },
  {
    "actualDate": "Monday, 1 January 2018",
    "mysqlDate": "2018-01",
    "phpDate": "2018-01",
    "datesMatch": true
  },
  {
    "actualDate": "Tuesday, 1 January 2019",
    "mysqlDate": "2019-01",
    "phpDate": "2019-01",
    "datesMatch": true
  },
  {
    "actualDate": "Wednesday, 1 January 2020",
    "mysqlDate": "2020-01",
    "phpDate": "2020-01",
    "datesMatch": true
  },
  {
    "actualDate": "Friday, 1 January 2021",
    "mysqlDate": "2020-53",
    "phpDate": "2020-53",
    "datesMatch": true
  },
  {
    "actualDate": "Saturday, 1 January 2022",
    "mysqlDate": "2021-52",
    "phpDate": "2021-52",
    "datesMatch": true
  }
]

test-formats.php

<?php
use Carbon\Carbon;

$startYear = 2000;
$endYear = date('Y');
$dayMonth = '1st January';

$readableFormat = 'l, j F Y';
$phpFormat = 'o-W';
$sqlFormat = '%x-%v';

$dates = collect();
for($year = $startYear; $year <= $endYear; $year++) {
  $dates->push(new Carbon($dayMonth . ' ' . $year)); 
};

$dates->map(function($date) use($phpFormat, $sqlFormat, $readableFormat) {
  $dateString = $date->format('Y-m-d');
  $mysqlDate = DB::selectOne("SELECT DATE_FORMAT('$dateString', '$sqlFormat') AS dated")->dated;
  $phpDate = $date->format($phpFormat);
  $actualDate = $date->format($readableFormat);
  $datesMatch = $phpDate === $mysqlDate;
  return compact('actualDate', 'mysqlDate', 'phpDate', 'datesMatch');
});