List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 28 1999 9:18pm
Subject:Re: About ISO week # formatting in MySQL
View as plain text  
On Tue, 1999-09-28 15:39:50 -0400, Bob Kline wrote:
> On Tue, 28 Sep 1999, Martin Ramsch wrote:
> 
> > 
> > Sure.  The complete ISO format "1981-W53" _is_ unambigous, but the
> > pure week number 53 is not - without having a second look at the date
> > (well, I wrote that already :-).
> > 
> 
> Why not something like ISOWEEK() (something along these lines was
> suggested elsewhere in this thread) which returns all the information
> ("198153" in this case)?

Actually I think this is a very good suggestion!

Only so far the main thread was about the WEEK() function, which gives
the number of the week within a year--without a year part!!!--, and
for this it would be just illogical to sometimes have January 1st
belonging to week 53, sometimes to week 1.  As somebody said, this
also would prevent proper sorting.  So I still think it's very
straight forward and 'right' that WEEK yields 0 for days before week 1.

> Well, it seems like a twist of reasoning to avoid the view that "the
> week before the first week in one year is the last week in the previous
> year," don't you think?

I agree, that this would be a twist of reasoning!
But actually I never said something like that, did I?

Without a year part, WEEK just gives an ordinal number with a meaning
within the year, without connection to the year before or after.

> 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 question ("Are these days within the same week?") is a new topic,
related but nonetheless different from "In which week of _this_ year
is a given day?".

Currently you could use
  FLOOR((TO_DAYS(d)-2)/7) AS gregorianianweeknum
to solve the 'I grant you is not possible' problem ;-)

> For the final irony, don't you think it's odd that it's the one of
> us on the far side of the Atlantic to be urging the use of the ISO
> approach, which is much more widely adopted in Europe?  :->}

Sorry, if you got that wrong impression, but quite the opposite is
true for me: I strongly support the ISO date format!  Though here in
Germany most people use our own format 28.09.1999 I try to get my
environment used to 1999-09-28.  But it's not easy to overcome old
habits ... ;)

I don't think that my arguments about the WEEK function say anything
against the ISO format, do they?  It's simply another beast than an
ISOWEEK function.

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