'Issue - different WEEK number showing from php WEEK function and mysql WEEK function?

I Want to get the current Week and Previous Week. I used to get the current week using php like below.

$week = date("W");

This will return the week number as 50.

If i use mysql like below 

select WEEK('2019-12-13') as this_week

I am getting the current week as 49

If i pass the $week in my sql database i am not getting any value for this week transaction.

Why this is happening and how can i get the current week and previous week accurately?

phpmysql


Solution 1:[1]

There are different rules for establishing week numbers, so check the default rules as they pertain to each of the relevant functions. MySQL's WEEK() function allows you to choose from a variety of 'modes' - rules which establish where weeks are counted from

select WEEK('2019-12-13');
+--------------------+
| WEEK('2019-12-13') |
+--------------------+
|                 49 |
+--------------------+

select WEEK('2019-12-13',1);
+----------------------+
| WEEK('2019-12-13',1) |
+----------------------+
|                   50 |
+----------------------+

Solution 2:[2]

MySQL and PHP by default use different algorithms to determine the week number.

Supposing you want to replicate in PHP MySQL's algorithm, i.e. get the same number that MySQL would return, then

/** MySQL's WEEK() function ISO8601-2 (European) */
function isodate($date) {
    list($y, $m, $d) = explode('-', $date);
    $timestamp  = mktime(12, 12, 12, $m, $d, $y);
    $doy        = date('z', $timestamp);
    $dow        = date('w', $timestamp);
    return floor((7+$doy-$dow)/7);
}

Always be sure to verify your algorithm:

$db     = new PDO('mysql:host=localhost; dbname=test;', 'user', 'pass');
$stmt   = $db->prepare("SELECT WEEK(?) AS x");
$diff   = 0;
$checks = 0;

print " Anno    |   01   |   02   |   03   |   04   |   05   |   06   |   07   |\n";
// Controllo solo Gennaio e Dicembre, approfittando wilmente del fatto che hanno entrambi 31 giorni
foreach ([ 1, 12 ] as $month) {
    for ($year = 1999; $year <= 2022; $year++) {
        for ($day = 1; $day <= 31; $day++) {
            // Build a timestamp from the date
            $date = mktime(12, 12, 12, $month, $day, $year);
            $intl = date('Y-m-d', $date);
            if ($day === 1) {
                print date('M Y ', $date);
            }
            // Ask the date to MySQL
            $stmt->execute([ $intl ]);
            $data  = $stmt->fetch();
            $mysql = (int)$data['x'];
            // $php   = date('W', $date);
            $php   = (int)isodate($intl);
            $checks ++;
            if ($mysql !== $php) {
                $diff++;
            }
            printf('|%s %02d/%d', date('D', $date), $php, $mysql);
        }
        print("|\n");
    }
}
print("{$checks} checks, {$diff} differences\n");

$ php -q test.php | grep "differences"

1488 checks, 0 differences

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Strawberry
Solution 2 LSerni

Related Questions