ISO-8601 Week Number

WEEK() does not return a valid ISO-8601 week number.

How-To-Repeat: Try
SELECT(20021019000000); # returns 42
SELECT(20021020000000); # returns 43, shouldn't be any larger than
20021019000000.

It should be relatively simple (see some of the links at
http://www.phys.uu.nl/~vgent/calendar/isocalendar.htm) to implement an
ISO-8601-compliant week number in MySQL. One could either create a new
WEEK() function, or borrow from PostgreSQL its "SET DateStyle 'ISO'".

This is a blocking issue for my company's further use of MySQL.

Dan Reif


Fairfield Language Technologies
Makers of the award-winning Rosetta Stone language-learning software.
Check us out at http://www.RosettaStone.com

- We have ways to make you think -


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12838@lists.mysql.com
To unsubscribe, e-mail

Re: ISO-8601 Week Number

Dan Reif writes:
> WEEK() does not return a valid ISO-8601 week number.
>
> How-To-Repeat: Try
> SELECT(20021019000000); # returns 42
> SELECT(20021020000000); # returns 43, shouldn't be any larger than
> 20021019000000.
>
> It should be relatively simple (see some of the links at
> http://www.phys.uu.nl/~vgent/calendar/isocalendar.htm) to implement an
> ISO-8601-compliant week number in MySQL. One could either create a new
> WEEK() function, or borrow from PostgreSQL its "SET DateStyle 'ISO'".
>
> This is a blocking issue for my company's further use of MySQL.
>
> Dan Reif
>

HI!

I guess you implied above :

SELECT WEEK(..);

What are you looking for is European version of the week, which starts
on Monday, for which you have to use another parameter in WEEK(), like
this:

mysql -e "select week('2002-10-19',1)"
+----------------------+
| week('2002-10-19',1) |
+----------------------+
| 42 |
+----------------------+

mysql -e "select week('2002-10-20',1)"
+----------------------+
| week('2002-10-20',1) |
+----------------------+
| 42 |
+----------------------+



--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12839@lists.mysql.com
To unsubscribe, e-mail

Re: ISO-8601 Week Number

On Wednesday 23 October 2002 21:20, Sinisa Milivojevic wrote:


Dan is right, week() doesn't support the ISO Standard like other RDBM's (its
not the first monday per year, 2. Jan. could also week nr 53 from last year).
Cause mysql week is incompatible to php which supports ISO-Standard we have
to look for another RDBMs too :( For not breaking BC WEEK_ISO () would be a
nice function.

dreif, does PostgreSQL supports the complete ISO 8601 Standard (also
formatted output)?

Regards

Hermann

> Dan Reif writes:
> > WEEK() does not return a valid ISO-8601 week number.
> >
> > How-To-Repeat: Try
> > SELECT(20021019000000); # returns 42
> > SELECT(20021020000000); # returns 43, shouldn't be any larger than
> > 20021019000000.
> >
> > It should be relatively simple (see some of the links at
> > http://www.phys.uu.nl/~vgent/calendar/isocalendar.htm) to implement an
> > ISO-8601-compliant week number in MySQL. One could either create a new
> > WEEK() function, or borrow from PostgreSQL its "SET DateStyle 'ISO'".
> >
> > This is a blocking issue for my company's further use of MySQL.
> >
> > Dan Reif
>
> HI!
>
> I guess you implied above :
>
> SELECT WEEK(..);
>
> What are you looking for is European version of the week, which starts
> on Monday, for which you have to use another parameter in WEEK(), like
> this:
>
> mysql -e "select week('2002-10-19',1)"
> +----------------------+
>
> | week('2002-10-19',1) |
>
> +----------------------+
>
> | 42 |
>
> +----------------------+
>
> mysql -e "select week('2002-10-20',1)"
> +----------------------+
>
> | week('2002-10-20',1) |
>
> +----------------------+
>
> | 42 |
>
> +----------------------+

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12846@lists.mysql.com
To unsubscribe, e-mail

Re: ISO-8601 Week Number

At 01:30 PM 10/24/2002 +0200, Hermann Mayer wrote:
>On Wednesday 23 October 2002 21:20, Sinisa Milivojevic wrote:
>
>
>Dan is right, week() doesn't support the ISO Standard like other RDBM's (its
>not the first monday per year, 2. Jan. could also week nr 53 from last year).
>Cause mysql week is incompatible to php which supports ISO-Standard we have
>to look for another RDBMs too :( For not breaking BC WEEK_ISO () would be a
>nice function.


Actually, the undocumented second argument to WEEK() (or at least
poorly-documented, since I couldn't find a reference to it) can be set to 1
to force ISO compliance:

SELECT WEEK( date, '1' ) FROM table_with_date;

Thanks go out to Mr. Milivojevic for alerting me to this.

>dreif, does PostgreSQL supports the complete ISO 8601 Standard (also
>formatted output)?

Don't know, sorry.

>Regards
>
>Hermann

Likewise,
Dan


> > Dan Reif writes:
> > > WEEK() does not return a valid ISO-8601 week number.
> > >
> > > How-To-Repeat: Try
> > > SELECT(20021019000000); # returns 42
> > > SELECT(20021020000000); # returns 43, shouldn't be any larger than
> > > 20021019000000.
> > >
> > > It should be relatively simple (see some of the links at
> > > http://www.phys.uu.nl/~vgent/calendar/isocalendar.htm) to implement an
> > > ISO-8601-compliant week number in MySQL. One could either create a new
> > > WEEK() function, or borrow from PostgreSQL its "SET DateStyle 'ISO'".
> > >
> > > This is a blocking issue for my company's further use of MySQL.
> > >
> > > Dan Reif
> >
> > HI!
> >
> > I guess you implied above :
> >
> > SELECT WEEK(..);
> >
> > What are you looking for is European version of the week, which starts
> > on Monday, for which you have to use another parameter in WEEK(), like
> > this:
> >
> > mysql -e "select week('2002-10-19',1)"
> > +----------------------+
> >
> > | week('2002-10-19',1) |
> >
> > +----------------------+
> >
> > | 42 |
> >
> > +----------------------+
> >
> > mysql -e "select week('2002-10-20',1)"
> > +----------------------+
> >
> > | week('2002-10-20',1) |
> >
> > +----------------------+
> >
> > | 42 |
> >
> > +----------------------+

Dan Reif
Fairfield Language Technologies
Makers of the award-winning Rosetta Stone language-learning software.
Check us out at http://www.RosettaStone.com

- We have ways to make you think -


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12847@lists.mysql.com
To unsubscribe, e-mail

Re: ISO-8601 Week Number

On Thursday 24 October 2002 13:49, Dan Reif wrote:

> Actually, the undocumented second argument to WEEK() (or at least
> poorly-documented, since I couldn't find a reference to it) can be set to 1
> to force ISO compliance:
>
> SELECT WEEK( date, '1' ) FROM table_with_date;

Definition of ISO 8601

The ISO 8601:1988 week number is a decimal number (range 01 through 53). ISO
weeks start with Monday and end with Sunday. Week 01 of a year is the first
week which has the majority of its days in that year; this is equivalent to
the week containing the year's first Thursday, and it is also equivalent to
the week containing January 4. Week 01 of a year can contain days from the
previous year. The week before week 01 of a year is the last week (52 or 53)
of the previous year even if it contains days from the new year. This format
is a POSIX.2 extension and also appears in ISO C99.

SELECT WEEK('2000-01-01', 1) returns 0, but it should be 52 or 53 (last week
from 1999), the same problems with December 31th.

Some months ago, I sent a patch for a ISO_WEEK function, but looks like
MySQL AB is not so interested to support popular standards.
If you like, I can sent you the patch or an UDF for ISO_WEEK.

Regards

Georg

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12848@lists.mysql.com
To unsubscribe, e-mail

Re: ISO-8601 Week Number

Dan Reif writes:
> At 01:30 PM 10/24/2002 +0200, Hermann Mayer wrote:
>
> Actually, the undocumented second argument to WEEK() (or at least
> poorly-documented, since I couldn't find a reference to it) can be set to 1
> to force ISO compliance:
>
> SELECT WEEK( date, '1' ) FROM table_with_date;
>
> Thanks go out to Mr. Milivojevic for alerting me to this.
>
> >dreif, does PostgreSQL supports the complete ISO 8601 Standard (also
> >formatted output)?
>
> Don't know, sorry.
>
> >Regards
> >
> >Hermann
>
> Likewise,
> Dan
>
>
> Dan Reif
> Fairfield Language Technologies
> Makers of the award-winning Rosetta Stone language-learning software.
> Check us out at http://www.RosettaStone.com

Hi!

First of all, second argument is introduced in 4.0.* and it is very
well documented :

`WEEK(date)'
`WEEK(date,first)'
With a single argument, returns the week for `date', in the range
`0' to `53' (yes, there may be the beginnings of a week 53), for
locations where Sunday is the first day of the week. The
two-argument form of `WEEK()' allows you to specify whether the
week starts on Sunday or Monday. The week starts on Sunday if the
second argument is `0', on Monday if the second argument is `1':

mysql> SELECT WEEK('1998-02-20');
-> 7
mysql> SELECT WEEK('1998-02-20',0);
-> 7
mysql> SELECT WEEK('1998-02-20',1);
-> 8
mysql> SELECT WEEK('1998-12-31',1);
-> 53


--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12850@lists.mysql.com
To unsubscribe, e-mail

Re: ISO-8601 Week Number

Hello.

On Thu 2002-10-24 at 14:05:35 +0200, georg.richter@php-ev.de wrote:
> On Thursday 24 October 2002 13:49, Dan Reif wrote:
>
> > Actually, the undocumented second argument to WEEK() (or at least
> > poorly-documented, since I couldn't find a reference to it)

http://www.mysql.com/doc/en/Date_and_time_functions.html
lists it explicitly and explains its use.

[...]
> SELECT WEEK('2000-01-01', 1) returns 0, but it should be 52 or 53 (last week
> from 1999), the same problems with December 31th.

The same, abovementioned page explains why they chose to return the
result they do and it is suggested to use YEARWEEK() instead and even
give an example how to extract the week portion.

> Some months ago, I sent a patch for a ISO_WEEK function, but looks like
> MySQL AB is not so interested to support popular standards.

And it looks like you jump to conclusions. But that does not mean
that's indeed the case.

> If you like, I can sent you the patch or an UDF for ISO_WEEK.

Or simply use

RIGHT(YEARWEEK(your_date),2)

Regards,

Benjamin.

--
benjamin-mysql@pflugmann.de

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12853@lists.mysql.com
To unsubscribe, e-mail

Re: ISO-8601 Week Number

On Thursday 24 October 2002 16:42, you wrote:
> Hello.
>
> On Thu 2002-10-24 at 14:05:35 +0200, georg.richter@php-ev.de wrote:
> > On Thursday 24 October 2002 13:49, Dan Reif wrote:
> > > Actually, the undocumented second argument to WEEK() (or at least
> > > poorly-documented, since I couldn't find a reference to it)
>
> http://www.mysql.com/doc/en/Date_and_time_functions.html
> lists it explicitly and explains its use.


Thx, looks like documentation has changed, and its explained more clearly now.

> And it looks like you jump to conclusions. But that does not mean
> that's indeed the case.

Nope, I already discussed this with Sinisa some months before, cause users
claimed about the incompatibility between php and mysql. Imho its easier to
change WEEK in your SQL-Code to ISO_WEEK than replace it with a substr
construct.

Regards

Georg

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12855@lists.mysql.com
To unsubscribe, e-mail

Re: ISO-8601 Week Number

Hi!

Some last notes about this thread:

>>>>> "Dan" == Dan Reif writes:

Dan> At 01:30 PM 10/24/2002 +0200, Hermann Mayer wrote:
>> On Wednesday 23 October 2002 21:20, Sinisa Milivojevic wrote:
>>
>>
>> Dan is right, week() doesn't support the ISO Standard like other RDBM's (its
>> not the first monday per year, 2. Jan. could also week nr 53 from last year).
>> Cause mysql week is incompatible to php which supports ISO-Standard we have
>> to look for another RDBMs too :( For not breaking BC WEEK_ISO () would be a
>> nice function.


Dan> Actually, the undocumented second argument to WEEK() (or at least
Dan> poorly-documented, since I couldn't find a reference to it) can be set to 1
Dan> to force ISO compliance:

Dan> SELECT WEEK( date, '1' ) FROM table_with_date;

From the manual:

------
With a single argument, returns the week for `date', in the range
`0' to `53' (yes, there may be the beginnings of a week 53), for
locations where Sunday is the first day of the week. The
two-argument form of `WEEK()' allows you to specify whether the
week starts on Sunday or Monday. The week starts on Sunday if the
second argument is `0', on Monday if the second argument is `1':

mysql> SELECT WEEK('1998-02-20');
-> 7
mysql> SELECT WEEK('1998-02-20',0);
-> 7
mysql> SELECT WEEK('1998-02-20',1);
-> 8
mysql> SELECT WEEK('1998-12-31',1);
-> 53

Note: in Version 4.0, `WEEK(#,0)' was changed to match the
calendar in the USA.

Note that if a week is the last week of the previous year, MySQL
will return 0:

mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
-> 2000, 0

One could argue that MySQL should return `52' for the `WEEK()'
function as the given date is actually the 52 second week of 1999.
We decided to return 0 instead as we want the function to return
'the week number in the given year'. This makes the usage of the
`WEEK()' function reliable when combined with other functions that
extracts a date part from a date.

If you would prefer to know the correct year-week, then you should
use the `YEARWEEK()' function instead:

mysql> SELECT YEARWEEK('2000-01-01');
-> 199952
mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
-> 52

----------

We shall look into adding a second bit to week() in 4.0.5 so that you
can get values in the range 1-53:

WEEK(X,2) -> Returns weeks 1-53, according to calendare in USA
WEEK(X,3) -> Returns weeks 1-53, according to calendar in Europe

This should satisfy most weeek usage.

Regards,
Monty

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12951@lists.mysql.com
To unsubscribe, e-mail

Re: ISO-8601 Week Number

Hi!

>>>>> "Georg" == Georg Richter writes:

Georg> On Thursday 24 October 2002 13:49, Dan Reif wrote:



Georg> SELECT WEEK('2000-01-01', 1) returns 0, but it should be 52 or 53 (last week
Georg> from 1999), the same problems with December 31th.

Georg> Some months ago, I sent a patch for a ISO_WEEK function, but looks like
Georg> MySQL AB is not so interested to support popular standards.
Georg> If you like, I can sent you the patch or an UDF for ISO_WEEK.

The reason we didn't want to change the old behaviour for WEEK() was
that we didn't want to cause unexpected problems for old MySQL users.

The ISO_WEEK() function has been on our TODO; The problem was mostly
to decide how to do this and fit this into our release schedule.

Anyway, for MySQL 4.0.6 we have now extended the optional arguments to
the WEEK() function and you will get the ISO behaviour by using 3 as
the optional argument to week():

From the updated manual:

------
`WEEK(date)'
`WEEK(date,first)'
With a single argument, returns the week for `date', in the range
`0' to `53' (yes, there may be the beginnings of a week 53), for
locations where Sunday is the first day of the week. The
two-argument form of `WEEK()' allows you to specify whether the
week starts on Sunday or Monday and if you the return value should
be in range `0-53' or `1-52'.

Here is a table for how the second arguments work:

*Value* `Meaning'
0 Week starts on Sunday and
return value is in range 0-53
1 Week starts on Monday and
return value is in range 0-53
2 Week starts on Sunday and
return value is in range 1-53
3 Week starts on Monday and
return value is in range 1-53 (ISO 8601)

mysql> SELECT WEEK('1998-02-20');
-> 7
mysql> SELECT WEEK('1998-02-20',0);
-> 7
mysql> SELECT WEEK('1998-02-20',1);
-> 8
mysql> SELECT WEEK('1998-12-31',1);
-> 53

Note: in Version 4.0, `WEEK(#,0)' was changed to match the
calendar in the USA.

Note that if a week is the last week of the previous year, MySQL
will return 0 if you don't use 2 or 3 as the optional argument:

mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
-> 2000, 0
mysql> SELECT WEEK('2000-01-01',2);
-> 52
------

Regards,
Monty

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12950@lists.mysql.com
To unsubscribe, e-mail