PHP date() as MySQL YEARWEEK()

Question

This question is pretty similar to this and this (and other) questions, but what I want to do is the opposite, I am expecting a PHP answer to show the same result than MySQL YEARWEEK().

Today is 2019-09-16 and MySQL YEARWEEK says:

SELECT YEARWEEK(NOW()) -- returns 201937

And PHP says:

echo date('oW') // returns 201938

What do I have to do in PHP to show "201937" just like MySQL YEARWEEK does?

possible duplicate -> https://stackoverflow.com/questions/15562270/php-datew-vs-mysql-yearweeknow — myxaxa, Sep 16 '19 at 15:19
I don't think PHP has the option to specify the start of the week (docs specifically say `ISO-8601 week number of year, weeks starting on Monday `), so you can't adjust it easily. — aynber, Sep 16 '19 at 15:20
They change every year, if year begins in monday php show it as week 52 and mysql as week 1, they also change between days, because weeks can change on sunday or monday (I think YEARWEEK changes on sunday and PHP on monday) — stramin, Sep 16 '19 at 15:21
Then you could try to get tomorrow's weeknumber in PHP to "align" it with MySQL? — Pepper, Sep 16 '19 at 15:23
@aynber, that's a problem, it sounds like I have to do it manually :( — stramin, Sep 16 '19 at 15:23
@Pepper, that sounds like a possibility, I wonder if that could give me any problem. — stramin, Sep 16 '19 at 15:25
@stramin After looking into it a bit more, there seem to be two issues, one being that weeks don't start the same day, and the other concerns the first week of the year. For MySQL it's the week including the first sunday of the year is week 1, anything before belongs to the previous year, while PHP seems to check if most (4+ days of the PHP week (monday -> sunday) are in the year or not. So if the year starts with a friday, saturday or sunday, those belong to last year, otherwise they belong to week 1 — Pepper, Sep 16 '19 at 16:29
If i'd really need it, and I'd need it to be 100% MySQL compatible, and it's not performance critical, I would probably just execute an SQL query. — Paul Spiegel, Sep 16 '19 at 17:09

score 2 · Accepted Answer · answered Sep 16 '19 at 17:57

There are two issues:

The weeks number changes on different days (Monday for PHP, Sunday for MySQL)
The first week of the year is determined differently (week containing the 4th of january for PHP, week starting with the first Sunday of the year for MySQL)

Which then causes these differences:

First day of year: | Mo | Tu | We | Th | Fr | Sa | Su |
         PHP week  | ------- 01 ------ | --- 52/53 -- |
       MySQL week  | ----------- 52/53 ----------| 01 |

We have 2 different cases, based on when the year starts:

If it starts a Friday, Saturday or Sunday, PHP start week 1 "late" by 1 day (it can be solved by taking tomorrow's week number in PHP)
It it starts a Monday, Tuesday, Wednesday or Thursday, you'll end up with an offset of 3-6 days, PHP being ahead this time, and it becomes a mess to solve.

So all in all, it seems you're better off just counting how many Sundays already passed this year, which apparently can be done with the following (adapted from this):

ceil((date("z") + 1 - date("w")) / 7); //added +1 in case the year starts a Sunday

And if it's 0 you count how many Sundays there were last year:

$ldly = strtotime(date("Y")-1)."-12-31"); //last day last year
ceil((date("z", $ldly ) + 1 - date("w", $ldly )) / 7);

By combining the two (and adding a parameter if you want to check dates other than today) you'd get:

function weekNum($date = false) {
    $day = $date ? strtotime($date) : time();
    if($res = ceil((date("z", $day) + 1 - date("w", $day)) / 7)){
        return $res;
    }
    $ldly = strtotime((date("Y", $day)-1)."-12-31"); //last day last year
    return ceil((date("z", $ldly ) + 1 - date("w", $ldly )) / 7);
}

I think this should always match with the MySQL date, but do your own tests to make sure! :)

Koala Yeung · Answer 2 · 2019-09-16T15:33:22.070

-1

Like this?


echo date('oW', time() - 86400); // basically use yesterday's week number

edited Sep 16 '19 at 15:33

answered Sep 16 '19 at 15:26

Koala Yeung

7,475
3
30
50

Weeks change every year, if year begins in monday php show it as week 52 and mysql as week 1, they also change between days, because weeks can change on sunday or monday (I think YEARWEEK changes on sunday and PHP on monday) – stramin Sep 16 '19 at 15:28
1

I don't think you need the `-1` after your edit, you'll end up with week 0, which doesn't exist, at the start of the year. (And if you remove the `-1` there is no need to split `o` and `W` anymore :) – Pepper Sep 16 '19 at 15:29
@stramin, from the looks of it, if a year begins on monday, MySQL will still return last year's week number: [fiddle](https://www.db-fiddle.com/f/XJRVZZ8Fez7ri4sVUe5aR/0) – Pepper Sep 16 '19 at 15:36
@KoalaYeung, Thank you for your time, for this date `2019-09-14`, `YEARWEEK` returns 201936 and PHP returns 201937, any other idea? (I used it this way: `date('oW', strtotime('2019-09-14') - 86400)` – stramin Sep 16 '19 at 15:44
@Pepper, you are right, it seems like 52 appear other day, check this date `SELECT YEARWEEK("2016-01-01")`, it returns 201552 (yes, the previous year) – stramin Sep 16 '19 at 15:55

PHP date() as MySQL YEARWEEK()

2 Answers2