List:General Discussion« Previous MessageNext Message »
From:Phil Date:August 20 2008 12:53pm
Subject:Re: convert week of the year into a date string
View as plain text  
I did something similar to this recently. I ended up using the following

select date_sub(curdate(), interval(dayofweek(curdate()) + (($week - week) *
7) - 1) DAY) as mydate.

This was in php and ahead of time I set $week as select week(curdate()).

It could easily be extended with year.

Phil

On Wed, Aug 20, 2008 at 5:31 AM, Joerg Bruehe <joerg@stripped> wrote:

> 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
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


-- 
Help build our city at http://free-dc.myminicity.com !

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