Week of year, create array with start and end timestamp

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Week of year, create array with start and end timestamp

Post by batfastad »

Hi everyone

I'm currently struggling with my week of year calculations.

Basically I'm designing a report which will break down figures into weekly sales figures.
The script gets the data from MySQL, then loops through the records, adding the various figures to different multi-dimensional array elements.

The user selects a start year/week number, and an end year/week number.
I convert these 2 into unix timestamps with the following function that I found somewhere on here I think:

Code: Select all

function week_timestamp($year, $week_no) {
 
    $offset = date('w', mktime(0, 0, 0, 1, 1, $year));
    $offset = ($offset < 5) ? 1-$offset : 8-$offset;
    $monday = mktime(0, 0, 0, 1, 1+$offset, $year);
 
    return strtotime('+' . ($week_no - 1) . ' weeks', $monday);
}
You provide the function with a year and week number, and it converts it to a timestamp.

For the end_stamp, I'm adding 604800 (60s x 60m x 24hours x 7days) to the value returned from the above function.
Because if the user wants to view the figures between week 30 and week 33, I need to be including the figures up to Sunday 23:59 of week 33 in my calculation.

That works and the data I'm returning is correct.

But the simple way I was looping through the MySQL data, meant I was getting a small problem with the report.
If the user ran the report for 2007 week 15 -> week 30 but there was no data for say weeks 28 and 29, I was not having an array element created for weeks 28 and 29.
For a sales report it was misleading because those weeks were just getting missed off the report... for our report it's better to have those rows displayed, but just showing 0 in each column.

So instead of creating the array as I looped through the MySQL results, I thought I'd try and set up the array at the beginning of the script...

Code: Select all

   // CREATE BLANK WEEKLY SALES ARRAY
    $curr = $start_stamp;
    while ($curr < $end_stamp) {
 
        $week_effective = date('Y W', $curr);
 
        $weekly_sales[$week_effective]['usd'] = 0;
        $weekly_sales[$week_effective]['eur'] = 0;
        $weekly_sales[$week_effective]['gbp'] = 0;
        $weekly_sales[$week_effective]['gbp_equiv'] = 0;
        $weekly_sales[$week_effective]['geo_usa'] = 0;
        $weekly_sales[$week_effective]['geo_row'] = 0;
        $weekly_sales[$week_effective]['sales_cat_1'] = 0;
        $weekly_sales[$week_effective]['sales_cat_2'] = 0;
        $weekly_sales[$week_effective]['sales_cat_3'] = 0;
        $weekly_sales[$week_effective]['sales_cat_4'] = 0;
        $weekly_sales[$week_effective]['sales_cat_5'] = 0;
        $weekly_sales[$week_effective]['sales_cat_6'] = 0;
        $weekly_sales[$week_effective]['sales_cat_7'] = 0;
        $weekly_sales[$week_effective]['curr'] = $curr;
 
        $curr += 604800;
    }
So my weekly_sales array is a multi-dimensional array with the key of the 1st dimension being the year and week number... eg: '2007 03', '2007 04', '2007 05' etc
Then the key of the 2nd dimension is the particular sales figures from the database.

So the purpose of this was to create my array before looping through my data.
It basically loops through values of curr, which is set to the start timestamp ($start_stamp), then adds 604800 seconds until $curr is >= the end timestamp ($end_stamp).
In theory I should get the array created with every week number between $start_stamp and $end_stamp.

So when I loop through my MySQL data, I just do the following...

Code: Select all

$weekly_sales[$create_stamp]['sales_cat_1'] += $rate_gbp;
So looping through MySQL is just adding to the array in existence.

The calculations and MySQL looping at this point work absolutely perfectly.

Here's the problem I've got...
When users do a report which wraps over a year eg: 2007 week 40 -> 2008 week 5
Then I'm getting an extra array item which corresponds to 2007 week 01!

The reason is because doing

Code: Select all

date('Y W', 1193616000)
returns '2007 01', even though the date is actually Monday, December 31st 2007, 0:00:00

Can anyone think of a way to solve this?
So when a user does a report that wraps over Dec/Jan, I don't get this 'surplus' row in my array?

Does anyone understand what I'm on about? :shock:

Thanks
Ben
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Week of year, create array with start and end timestamp

Post by pickle »

1193616000 = Sun, 28 Oct 2007 18:00:00 -0600 for me - not sure what's up with that.

Anyway, is this errant entry being created after you've looped through your MySQL data or after your while loop that creates the elements?

Finally, I'd generally recommend against adding a scalar value & hoping it's the right number of seconds - that doesn't account for DST. Rather than

Code: Select all

$curr += 604800;
use

Code: Select all

$curr = strtotime('+1 week',$curr);
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: Week of year, create array with start and end timestamp

Post by batfastad »

Errm, I messed up... the timestamp of the random entry appearing in my array 1199059200... that one translates as 31st Dec
It's the value of $curr of one of the rows in the array.

This entry is being created when I run that while() loop which initially populates the array with the 1st dimension values.

It's not from the MySQL data.
It doesn't happen though when I just loop through the MySQL data and build the array at that time.
So it's something to do with the quick and dirty while() loop I'm running.

I have found a temporary fix though, rathing than adding 604800 seconds to $curr in line 22 above, I add 604799
That means rather than having the value 1199059200 which translates to 31st December 2007 00:00 (GMT) which is week 01... giving '2007 01'
I get 1199059199 which translates to 31st December 2007 23:59 (GMT) which gives '2007 52', which is what I'm looking for.

Good tip on using strtotime instead... I'm guessing that will deal ok with DST then?
Whereas there's not 604800 seconds in a week where DST comes/goes!

Thanks
Ben
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Week of year, create array with start and end timestamp

Post by pickle »

Dude, that is weird, but documented & makes perfect sense ;)

http://en.wikipedia.org/wiki/ISO_8601#Week_dates

Since you're passing 'Y' and 'W' as separate entities, they don't rely on each other. So, date() figures out the year, which for December 31st is 2007. Then it figures out the week number which, according to spec (linked above) is 01 since it's the first week of '08.

If you're using PHP > 5.10, you can use 'o' as an argument to date() rather than 'Y'.
PHP Manual wrote: 'o' ISO-8601 year number. This has the same value as Y, except that if the ISO week number (W) belongs to the previous or next year, that year is used instead. (added in PHP 5.1.0)
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: Week of year, create array with start and end timestamp

Post by batfastad »

Ah ok, that'll probably solve my problem.
I'll have to experiment, I'm sure there'll be some permutation of years/weeks where I'll get a problem but hopefully I should be able to get that sorted by using strtotime() instead of adding seconds, and using o instead of Y.

Thanks for the help!! :D
Post Reply