List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:August 20 2008 9:31am
Subject:Re: convert week of the year into a date string
View as plain text  
Pintér Tibor wrote:
> 
> Ananda Kumar írta:
>> Hi All,
>> I think i worked on this and found the results.
>>
>>
>> I did the below.
>>
>> 1. Multiplied the week_of_the_year with 7 (7 days per week), to get the
>> total number of days from begning of the year.
>>
>> 2. used mysql function makedate
>>    makedate(year,number of days from the start of the year)
>>   makedate(2008,224)
>> select makedate(2008,224);
>> +--------------------+
>> | makedate(2008,224) |
>> +--------------------+
>> | 2008-08-11         |
> 
> this is definitely wrong, since you dont care about the fact that the 
> frist day of the year is not always Monday

I agree it is wrong, but for a slightly different reason:
    What is the definition of "week of year" ?
One problem is that the weekday of January 1 varies, the other is that 
the definition of "week" may not be universal (does it start with Sunday 
or Monday ?).

I know of one widespread definition that (AFAIR) is (loosely)
"The first week which has more than half of its days in a given year is 
called 'week 1' of that year."
If you take Sunday as the start of the week, this translates to
"Week 1 is the week which contains the first Wednesday of a year."
(If your week starts Monday, the first Thursday determines it.)

There is another definition that (loosely) says
"The first week which has all its days in a given year is called 'week 
1' of that year."
Again, it is a separate question whether your weeks start Sunday or Monday.

By both definitions, January 1 need not belong to week 1, it may belong 
to the last week of the previous year.

See these lines quoted from Linux "man date":
> ~> man date | grep week
>        ...
>        %g     last two digits of year of ISO week number (see %G)
>        %G     year of ISO week number (see %V); normally useful only with %V
>        %u     day of week (1..7); 1 is Monday
>        %U     week number of year, with Sunday as first day of week (00..53)
>        %V     ISO week number, with Monday as first day of week (01..53)
>        %w     day of week (0..6); 0 is Sunday
>        %W     week number of year, with Monday as first day of week (00..53)

According to Stevens ("Advanced Programming in the Unix environment", 
page 158), the "%U" and "%W" codes seem to use the "all days" (second) 
definition.

I *guess* that the separate "ISO" reference implies that the ISO 
definition uses the "more than half" (first) definition, but I propose 
you check yourself.  From some references, I take it that is ISO 8601.


To return to the original question:
Sorry, I do not know a MySQL function to do that mapping.
Your application language might offer something:

 From C, "strftime()" and/or "strptime()" might help.
 From Perl, I assume you can find something in CPAN.
 From other languages, I have no idea off-hand.


But before coding anything, you have to check your definition of "week 
number", there are several to choose from.


Regards,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg@stripped   (+49 30) 417 01 487
Sun Microsystems GmbH,   Sonnenallee 1,   D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering     Muenchen: HRB161028

Thread
convert week of the year into a date stringAnanda Kumar19 Aug
Re: convert week of the year into a date stringAnanda Kumar20 Aug
  • Re: convert week of the year into a date stringPintér Tibor20 Aug
    • Re: convert week of the year into a date stringJoerg Bruehe20 Aug
      • Re: convert week of the year into a date stringPhil20 Aug