List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 28 1999 10:47pm
Subject:Re: About ISO week # formatting in MySQL
View as plain text  
On Tue, 1999-09-28 15:39:50 -0400, Bob Kline wrote:
> For one thing, don't you want the answer to the question "is this
> day in the same week as that day?" to give you a useful answer
> (which I grant you is not possible with the current approach of
> returning only the week, whether they get that "right" or not)?

This is what I found so far to deal with weeks, shown with some
example dates:

  SELECT DAYNAME(d)              AS day
       , d                       AS date
       , WEEK(d,1)               AS week
       , DATE_FORMAT( SUBDATE(d, INTERVAL WEEKDAY(d)-3 DAY)
                     ,'%Y%u' )   AS isoweek
       , FLOOR((TO_DAYS(d)-2)/7) AS weeknum
  FROM datesamples
  ORDER BY d;
  +-----------+------------+------+---------+---------+
  | day       | date       | week | isoweek | weeknum |
  +-----------+------------+------+---------+---------+
  | Thursday  | 1997-12-25 |   52 | 199752  |  104249 |
  | Friday    | 1997-12-26 |   52 | 199752  |  104249 |
  | Saturday  | 1997-12-27 |   52 | 199752  |  104249 |
  | Sunday    | 1997-12-28 |   52 | 199752  |  104249 |
  | Monday    | 1997-12-29 |   53 | 199801  |  104250 |
  | Tuesday   | 1997-12-30 |   53 | 199801  |  104250 |
  | Wednesday | 1997-12-31 |   53 | 199801  |  104250 |
  | Thursday  | 1998-01-01 |    1 | 199801  |  104250 |
  | Friday    | 1998-01-02 |    1 | 199801  |  104250 |
  | Saturday  | 1998-01-03 |    1 | 199801  |  104250 |
  | Sunday    | 1998-01-04 |    1 | 199801  |  104250 |
  | Monday    | 1998-01-05 |    2 | 199802  |  104251 |
  | Tuesday   | 1998-01-06 |    2 | 199802  |  104251 |
  | Wednesday | 1998-01-07 |    2 | 199802  |  104251 |
  | Thursday  | 1998-01-08 |    2 | 199802  |  104251 |
  | Friday    | 1998-12-25 |   52 | 199852  |  104301 |
  | Saturday  | 1998-12-26 |   52 | 199852  |  104301 |
  | Sunday    | 1998-12-27 |   52 | 199852  |  104301 |
  | Monday    | 1998-12-28 |   53 | 199853  |  104302 |
  | Tuesday   | 1998-12-29 |   53 | 199853  |  104302 |
  | Wednesday | 1998-12-30 |   53 | 199853  |  104302 |
  | Thursday  | 1998-12-31 |   53 | 199853  |  104302 |
  | Friday    | 1999-01-01 |    0 | 199853  |  104302 |
  | Saturday  | 1999-01-02 |    0 | 199853  |  104302 |
  | Sunday    | 1999-01-03 |    0 | 199853  |  104302 |
  | Monday    | 1999-01-04 |    1 | 199901  |  104303 |
  | Tuesday   | 1999-01-05 |    1 | 199901  |  104303 |
  | Wednesday | 1999-01-06 |    1 | 199901  |  104303 |
  +-----------+------------+------+---------+---------+

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