List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 28 1999 11:07am
Subject:Re: About ISO week # formatting in MySQL
View as plain text  
On Tue, 1999-09-28 12:11:14 +0200, Peter Strömberg wrote:
> Den 27 Sep 99, klockan 18:29
> skrev bkline om (RE: About ISO week # formatting in ):
[...]
> > The problem is that MySQL determines the first week of the year
> > based on the week with the first Sunday or Monday, but ISO says
> > the first week of the year is the week containing the first
> > Thursday (or some day closer to the middle of the week -- this
> > is from memory, so I may have it all muddled myself!).
> > 
> 
> $ cal 1 1987
>    January 1987
>  S  M Tu  W Th  F  S
>              1  2  3
>  4  5  6  7  8  9 10
> 11 12 13 14 15 16 17
> 18 19 20 21 22 23 24
> 25 26 27 28 29 30 31

As ISO weeks don't start with Sunday but with Monday, the calendars
better should be re-formatted:

    January 1987                      January 1982     
  M Tu  W Th  F  S  S               M Tu  W Th  F  S  S
           1  2  3  4                           1  2  3
  5  6  7  8  9 10 11               4  5  6  7  8  9 10
 12 13 14 15 16 17 18              11 12 13 14 15 16 17
 19 20 21 22 23 24 25              18 19 20 21 22 23 24
 26 27 28 29 30 31                 25 26 27 28 29 30 31

> $ mysql -usqluser -e"select week('1987-01-01',1), week('1987-01-04',1),
> week('1987-01-05',1)"
> +----------------------+----------------------+----------------------+
> | week('1987-01-01',1) | week('1987-01-04',1) | week('1987-01-05',1) |
> +----------------------+----------------------+----------------------+
> |                    1 |                    1 |                    2 |
> +----------------------+----------------------+----------------------+
> 
> Which is ok, but:
> 
> $ cal 1 1982
> 
> $ mysql -usqluser -e"select week('1982-01-01',1), week('1982-01-03',1),
> week('1982-01-04',1)"
> +----------------------+----------------------+----------------------+
> | week('1982-01-01',1) | week('1982-01-03',1) | week('1982-01-04',1) |
> +----------------------+----------------------+----------------------+
> |                    0 |                    0 |                    1 |
> +----------------------+----------------------+----------------------+
> 
> 
> week 0 ???

That's correct, because ISO week one is the first week that belongs to
more than 50% to the new year, which means the first week with it's
Thursday belonging to the new year.  If the year has some days before
this week, these are in week 0.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
About ISO week # formatting in MySQLJuan Riera26 Sep
  • About ISO week # formatting in MySQLMichael Widenius26 Sep
  • RE: About ISO week # formatting in MySQLJuan Riera27 Sep
    • RE: About ISO week # formatting in MySQLAldrian Gintingsuka27 Sep
    • RE: About ISO week # formatting in MySQLMichael Widenius27 Sep
      • RE: About ISO week # formatting in MySQLbkline28 Sep
        • RE: About ISO week # formatting in MySQLPeter Strömberg28 Sep
      • Re: About ISO week # formatting in MySQLMartin Ramsch28 Sep
        • Re: About ISO week # formatting in MySQLPeter Strömberg28 Sep
          • Re: About ISO week # formatting in MySQLAldrian Gintingsuka28 Sep
            • Re: About ISO week # formatting in MySQLMartin Ramsch28 Sep
            • Re: About ISO week # formatting in MySQLBob Kline28 Sep
          • Re: About ISO week # formatting in MySQLMatthias Urlichs28 Sep
            • Re: About ISO week # formatting in MySQLPeter Strömberg28 Sep
              • Re: About ISO week # formatting in MySQLMichael Widenius29 Sep
            • Re: About ISO week # formatting in MySQLBob Kline28 Sep
              • Re: About ISO week # formatting in MySQLMatthias Urlichs29 Sep
          • RE: About ISO week # formatting in MySQLJuan Riera30 Sep
            • Re: About ISO week # formatting in MySQLMartin Ramsch30 Sep
        • Re: About ISO week # formatting in MySQLMartin Ramsch28 Sep
          • Re: About ISO week # formatting in MySQLBob Kline28 Sep
            • Re: About ISO week # formatting in MySQLMartin Ramsch28 Sep
              • Re: About ISO week # formatting in MySQLBob Kline28 Sep
                • Re: About ISO week # formatting in MySQLMartin Ramsch28 Sep
                  • Re: About ISO week # formatting in MySQLBob Kline29 Sep
                • Re: About ISO week # formatting in MySQLMartin Ramsch29 Sep