Week of year, create array with start and end timestamp
Posted: Mon Jan 14, 2008 9:18 am
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:
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...
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...
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 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?
Thanks
Ben
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);
}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;
}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;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)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?
Thanks
Ben