Week Date Formats

Question

I have a difficult time, based on the documentation, figuring out how to compare the number of weeks between Perl and MySQL.

How would I calculate the exact same week number in Perl and MySQL based on the 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 show the same week numbers for any given timestamp. Ideally, I would like the week number to be something portable, like ISO 8601 . Although the number of weeks in my testing sometimes seems like a coincidence, I can't find anything in the documentation for Perl and MySQL that confirms that date formatting adheres to the same definition clearly.

Thanks!

Answer 1

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

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

Please note that the year matters because the date may be a week for the previous or next year. This is because of the ISO standard for the "week" where the first week of the year is such that it is the fourth day of January. Thus, a date like January 1 may be in the last week of the previous year.

For an unprepared eye, it looks like the MySQL YEARWEEK() function can do the same, or Mikey1980 suggests , try the WEEKOFYEAR() function. It seems that YEARWEEK() does the same thing its documentation says:

The resulting year may differ from the year in the date argument for the first and last week of the year.

... but I cannot guarantee that they are doing the same. :-)

Answer 2

For MySQL, try this query:

 SELECT WEEKOFYEAR(from_unixtime(datefield)) FROM table; 

Sorry, only 1/2 the answer you are looking for, but I hope this helps!