Re: Postgresql likes Tuesday...

Rod Taylor <rbt(at)rbt(dot)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