Re: Postgresql likes Tuesday...

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 of
week"
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(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz