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]
