ISO Week Date
It’s easy enough in PHP to get the week number for a given date using the ‘W’ value in the date() function. But how do you get the date of the first day of a week, given only the week number? And when we say first day of the week, we mean Monday, according to ISO 8601.
Figuring out the algorithm was complicated by a few factors:
- The use of date(’w') is required, which indexes the week starting on Sunday, and not Monday (argh)
- ISO 8601 states “If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53 of the previous year.”
Here is the php code:
$yearOffset = date('w',mktime(0,0,1,1,1,$year));
$dayCount = ($yearOffset * -1) + ($yearOffset < 2 ? 2 : 9);
$secCount = (3600*24*7*($week-($dayCount >= 5 ? 2 : 1)));
$date = date(’m/d/Y’,mktime(0,0,1,1,$dayCount,$year) + $secCount);
And here is a stored procedure for use at the database level:
DROP FUNCTION getdatefromweek(FLOAT, FLOAT) CASCADE;
CREATE FUNCTION getdatefromweek(FLOAT, FLOAT) RETURNS TIMESTAMP AS '
DECLARE
year_offset INTEGER;
day_count INTEGER;
sec_count INTEGER;
BEGIN
year_offset := EXTRACT(DOW FROM date ($1 || ''-01-01''));
day_count := (year_offset * -1) + CASE WHEN year_offset < 2 THEN 2 ELSE 9 END;
sec_count := 3600*24*7*($2 - CASE WHEN day_count >= 5 THEN 2 ELSE 1 END);
RETURN TO_CHAR(date ($1 || ”-01-01”) - (interval ”1 day”) + (interval ”1 day” * day_count) + (interval ”1 second” * sec_count), ”YYYY-MM-DD”);
END
‘
LANGUAGE ‘plpgsql’;
Hope this helps.
Tags: php sql
June 13th, 2007 at 6:47 pm
So I had some free time and I found you can just pass an ISO formatted string to the strtotime() function. Now you don’t have to do any calculations :)
Basically it works like this:
// use "strtotime('2007W011')" for Monday "1", of Week 1 ("01") of year 2007 ("2007")
function week2date($year, $week, $day = 1) {
$string = $year . 'W' . sprintf("%02d", $week) . $day;
return strtotime($string);
}