Thread: Postgresql likes Tuesday...
select to_char( to_date( CAST(extract(week from CURRENT_TIMESTAMP) as text) || CAST(extract(year from CURRENT_TIMESTAMP)as text) , 'WWYYYY') , 'FMDay, D'); to_char ------------Tuesday, 3 (1 row) Not that it matters for me at the moment (I care that it's in the week of..), but why does it pick Tuesday? -- Rod Taylor
Rod Taylor <rbt@rbt.ca> writes:
> select to_char(
> to_date(
> CAST(extract(week from CURRENT_TIMESTAMP) as text)
> || CAST(extract(year from CURRENT_TIMESTAMP) as text)
> , 'WWYYYY')
> , 'FMDay, D');
> to_char
> ------------
> Tuesday, 3
> (1 row)
> Not that it matters for me at the moment (I care that it's in the week
> of..), but why does it pick Tuesday?
The middle part of that boils down (as of today) to
regression=# select to_date('402002', 'WWYYYY'); to_date
------------2002-10-01
(1 row)
and Oct 1 (tomorrow) is Tuesday. As to why it picks that day to
represent Week 40 of 2002, it's probably related to the fact that Week 1
of 2002 is converted to
regression=# select to_date('012002', 'WWYYYY'); to_date
------------2002-01-01
(1 row)
which was a Tuesday.
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:
regression=# select extract(week from date '2001-12-31');date_part
----------- 1
(1 row)
Anyone able to check this stuff on Oracle? What exactly are the
boundary points for EXTRACT(week), and does to_date() agree?
regards, tom lane
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
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 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
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
On Mon, Sep 30, 2002 at 06:31:15PM -0400, Tom Lane wrote:
> The middle part of that boils down (as of today) to
>
> regression=# select to_date('402002', 'WWYYYY');
> to_date
> ------------
> 2002-10-01
> (1 row)
>
> and Oct 1 (tomorrow) is Tuesday. As to why it picks that day to
> represent Week 40 of 2002, it's probably related to the fact that Week 1
> of 2002 is converted to
>
> regression=# select to_date('012002', 'WWYYYY');
> to_date
> ------------
> 2002-01-01
> (1 row)
>
> which was a Tuesday.
>
> 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:
>
> regression=# select extract(week from date '2001-12-31');
> date_part
> -----------
> 1
> (1 row)
>
>
> Anyone able to check this stuff on Oracle? What exactly are the
> boundary points for EXTRACT(week), and does to_date() agree?
Please, read docs -- to_() functions know two versions of "number ofweek" IW = iso-week WW = "oracle" week
test=# select to_date('402002', 'WWYYYY'); to_date
------------2002-10-01
(1 row)
test=# select to_date('402002', 'IWYYYY'); to_date
------------2002-09-30
(1 row)
test=# select to_date('012002', 'WWYYYY'); to_date
------------2002-01-01
(1 row)
test=# select to_date('012002', 'IWYYYY'); to_date
------------2001-12-31
(1 row) Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz
On Mon, Sep 30, 2002 at 05:37:47PM -0400, Rod Taylor wrote:
> select to_char(
> to_date(
> CAST(extract(week from CURRENT_TIMESTAMP) as text)
> || CAST(extract(year from CURRENT_TIMESTAMP) as text)
> , 'WWYYYY')
> , 'FMDay, D');
>
> to_char
> ------------
> Tuesday, 3
> (1 row)
>
The PostgreSQL not loves Thuesday, but WW for year 2002 loves it. Why?
Because 'WW' = (day_of_year - 1) / 7 + 1, other words this yearstart on Thuesday (see 01-JAN-2002) and WW start weeks
each7 daysafter this first day of year.
If you need "human" week you must use IW (iso-week) that start everyMonday. I know there're countries where week start
onSunday, but it's not supported -- the problem is with 'D' it returns day-of-week for Sunday-based-week.
Your example (I use to_xxx () only, it's more readable):
If you need correct for Sunday-based-week:
select to_char( to_date(to_char(now(), 'IWYYYY'), 'IWYYYY')-'1d'::interval, 'FMDay, D'); to_char
-----------Sunday, 1
If you need Monday-based-week (ISO week):
test=# select to_char( to_date(to_char(now(), 'IWYYYY'), 'IWYYYY'), 'FMDay, D'); to_char
-----------Monday, 2
'2' is problem -- maybe add to to_xxx() functions 'ID' as day-of-isoweek.It's really small change I think we can do it
for7.3 too.
What think about it our Toms?
In the Oracle it's same (means WW vs. IW vs. D)
SVRMGR> select to_char(to_date('30-SEP-02'), 'WW IW Day D') from dual; TO_CHAR(TO_DATE('
----------------- 39 40 Monday 2
test=# select to_char('30-SEP-02'::date, 'WW IW Day D'); to_char -------------------
39 40 Monday 2
SVRMGR> select to_char(to_date('29-SEP-02'), 'WW IW Day D') from dual; TO_CHAR(TO_DATE('
----------------- 39 39 Sunday 1
test=# select to_char('29-SEP-02'::date, 'WW IW Day D'); to_char -------------------
39 39 Sunday 1
Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz
Karel Zak <zakkr@zf.jcu.cz> writes:
> What think about it our Toms?
> ...
> In the Oracle it's same (means WW vs. IW vs. D)
If it works the same as Oracle then I'm happy with it; that's what it's
supposed to do.
The real point here seems to be that EXTRACT(week) corresponds to
to_date's IW conversion, not WW conversion. This is indeed implied by
the docs, but it's not stated plainly (there's just a reference to ISO
in each of the relevant pages). Perhaps we need more documentation, or
a different layout that would offer a place to put notes like this one.
regards, tom lane
If it's of any use the following link gives some info on different schemes and details on an ISO week numbering standard. http://www.merlyn.demon.co.uk/weekinfo.htm#WkNo Best Regards, Tim Knowles