CODEWITHSUNDEEP
phpmysql
Mujahidh
Mujahidh

Reputation: 470

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?

Upvotes: 3

Views: 453

Answers (3)

LSerni
LSerni

Reputation: 57463

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

Upvotes: 1

juergen d
juergen d

Reputation: 204924

WEEK(date[,mode])

This function returns the week number for date. The two-argument form of WEEK() enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53.

MySQL docs

So your weeks on MySQL are zero-based because the default_week_format is 0.
Use 3 as parameter to get your desired result:

select week(curdate(), 3) as this_week

To get the last week you could do

select week(curdate() - interval 7 day, 3) as last_week

Upvotes: 4

Strawberry
Strawberry

Reputation: 33935

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 |
+----------------------+

Upvotes: 2

Related Questions