Re: Postgresql likes Tuesday...
On Tue, 2002-10-01 at 03:31, Tom Lane wrote:
Offhand this seems kinda inconsistent to me --- I'd expect
regression=# select extract(week from date '2002-09-30');
date_part
-----------
40
(1 row)to produce 39, not 40, on the grounds that the first day of Week 40
is tomorrow not today. Alternatively, if today is the first day of
Week 40 (as EXTRACT(week) seems to think), then ISTM that the to_date
expression should produce today not tomorrow.I notice that 2001-12-31 is considered part of the first week of 2002,
which is also pretty surprising:
There are at least 3 different ways to start week numbering:
1. from first week with any days in current year
2. from first full week in current year
3. from first week with thursday in current year
perhaps more...
I suspect it depends on locale which should be used.
---------------
Hannu
Import Notes
Reply to msg id not found: 12235.1033425075@sss.pgh.pa.usReference msg id not found: 1033421868.66248.62.camel@jesterReference msg id not found: 12235.1033425075@sss.pgh.pa.us
On Tue, 2002-10-01 at 03:49, Tom Lane wrote:
Hannu Krosing <hannu@tm.ee> writes:
On Tue, 2002-10-01 at 03:31, Tom Lane wrote:
I notice that 2001-12-31 is considered part of the first week of 2002,
which is also pretty surprising:There are at least 3 different ways to start week numbering:
...
I suspect it depends on locale which should be used.Perhaps. But I think there are two distinct issues here. One is
whether EXTRACT(week) is assigning reasonable week numbers to dates;
this depends on your convention for which day is the first of a week
as well as your convention for the first week of a year (both possibly
should depend on locale as Hannu suggests). The other issue is what
to_date(...,'WWYYYY') should do to produce a date representing a week
number. Shouldn't it always produce the first date of that week?
Producing middle-of-the week date is least likely to get a date in last
year.
Also should
select to_timestamp('01102002','DDMMYYYY');
also produce midday (12:00) for time, instead of current 00:00 ?
-----------------
Hannu
Import Notes
Reply to msg id not found: 12398.1033426174@sss.pgh.pa.usReference msg id not found: 1033421868.66248.62.camel@jesterReference msg id not found: 12235.1033425075@sss.pgh.pa.us
Hannu Krosing <hannu@tm.ee> writes:
On Tue, 2002-10-01 at 03:31, Tom Lane wrote:
I notice that 2001-12-31 is considered part of the first week of 2002,
which is also pretty surprising:
There are at least 3 different ways to start week numbering:
...
I suspect it depends on locale which should be used.
Perhaps. But I think there are two distinct issues here. One is
whether EXTRACT(week) is assigning reasonable week numbers to dates;
this depends on your convention for which day is the first of a week
as well as your convention for the first week of a year (both possibly
should depend on locale as Hannu suggests). The other issue is what
to_date(...,'WWYYYY') should do to produce a date representing a week
number. Shouldn't it always produce the first date of that week?
If not, what other conventions make sense?
regards, tom lane
On Mon, Sep 30, 2002 at 06:49:34PM -0400, Tom Lane wrote:
| The other issue is what
| to_date(...,'WWYYYY') should do to produce a date representing a week
| number. Shouldn't it always produce the first date of that week?
| If not, what other conventions make sense?
IMHO, it should choose the "Week Ending" date. This is
usually what all of the companies that I've worked with
want to see for the "day" column. For example, the
defect^H^H^H^H^H^H quality reports at Ford Motor in 1993
used a Predo of part by defect by week-ending. Where
week ending date was the Sunday following the work
week (monday-sunday). In various project data in
companies that I've worked with before and after 1993
I've yet to see a "weekly" report that didn't give
the week ending... alhtough some did use Friday or
Saturday for the week ending.
One hickup with this choice is that you'd probably
want the time portion to be 23:59:59.999 so that it
includes everything up to the end of the day. Hmm.
Clark