Reputation: 470
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
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
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.
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
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