Re: convert week of the year into a date string
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 <[EMAIL PROTECTED]> 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, [EMAIL PROTECTED] (+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/[EMAIL PROTECTED]
>
>
--
Help build our city at http://free-dc.myminicity.com !
Re: convert week of the year into a date string
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, [EMAIL PROTECTED] (+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/[EMAIL PROTECTED]
Re: convert week of the year into a date string
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 or do I misunderstand something? t -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: convert week of the year into a date string
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 | Thanks a lot for all your help. regards anandkl On 8/19/08, Phil <[EMAIL PROTECTED]> wrote: > > Ananda, > > I did something fairly similar to this just a few weeks ago. > > Unfortunately I don't have access to the server I did the script on until > later tonight (EST). > > If no one has replied by then I will dig out the script and send it on to > you. > > Phil > > On Tue, Aug 19, 2008 at 7:58 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > >> HI All, >> I have table which stores week_of_the_year and year. >> For example >> >> week_of_yearYEAR >> 32 2008 >> >> I want to convert this into the date. That is , i need to get the date of >> first day of the week 32. >> some thing like >> >> 03-AUG-2008. >> >> Regards >> anandkl >> > > > > -- > Help build our city at http://free-dc.myminicity.com ! > >
convert week of the year into a date string
HI All, I have table which stores week_of_the_year and year. For example week_of_yearYEAR 32 2008 I want to convert this into the date. That is , i need to get the date of first day of the week 32. some thing like 03-AUG-2008. Regards anandkl
