Date formats for weeks

Question

I'm having a difficult time, based on the documentation, of figuring out how to equate week numbers between Perl and MySQL.

How would I calculate the same exact week number in both Perl and MySQL based on a unix timestamp in the same time zone?

SELECT DATE_FORMAT(from_unixtime(datefield), '%Y%U') FROM table;

and

print strftime('%Y%U', localtime($datevar));

should produce identical week numbers for any given timestamp. Ideally I'd like the week number to be something portable, such as ISO 8601. While the week numbers in my testing seem to match up sometimes, I can't find anything in the documentation for both Perl and MySQL that confirms that the date formatting clearly adheres to the same definition.

Thanks!

Answer 1

For MySQL try this Query:

SELECT WEEKOFYEAR(from_unixtime(datefield)) FROM table;

Sorry it's only 1/2 the answer your looking for, but I hope it helps!

Answer 2

For Perl, look into the DateTime module. It supplies a week() method, that can return the week number of the year for a given DateTime object:

($week_year, $week_number) = $dt->week;

Note that the year matters, as a date can be in a week for the previous or next year. This is because of the ISO standard for "week", where the first week of the year is the one that contains the fourth day of January. Thus a date like January 1 could be in the final week of the previous year.

To the untrained eye it looks like MySQL's YEARWEEK() function could do the same thing, or as Mikey1980 suggests, try the WEEKOFYEAR() function. It looks like YEARWEEK() does the same thing, as its documentation says:

The year in the result may be different from the year in the date argument for the first and the last week of the year.

...but I can't guarantee they do the same thing. :-)