List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 30 1999 5:58am
Subject:Re: About ISO week # formatting in MySQL
View as plain text  
On Wed, 1999-09-29 22:53:22 +0200, Juan Riera wrote:
> To me, there are two things to note: first, we may need a function
> grouping data on weeks across different years, and second, we may
> need a function that respects ISO week formatting, and so week
> 1999-0 should be week 1998-53 (it could be 52 on other years) on
> this new function
> (I support ISOWEEK() name, as Peter has suggested ...)

Until there is such an ISOWEEK function, you could you
 FLOOR((TO_DAYS(date)-2)/7)
        giving you a unique week number, or
 DATE_FORMAT( ADDDATE(date, INTERVAL 3-WEEKDAY(date) DAY), '%Y-%u' )
        giving you the date in almost ISO year-week format.

To get the real ISO format '1998-W53' with the 'W' one has to do a
little trick, because DATE_FORMAT unfortunately doesn't allow to
include 'W' directly:
  REPLACE( DATE_FORMAT(ADDDATE(date, INTERVAL 3-WEEKDAY(date) DAY),'%Y-.%u')
          ,'.','W')

All of these expression enable you to group on weeks including weeks
across years.

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