Re: Postgresql likes Tuesday...

From: Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Rod Taylor <rbt(at)rbt(dot)ca>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Postgresql likes Tuesday...
Date: 2002-10-01 06:26:55
Message-ID: 20021001082654.C19642@zf.jcu.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Dave Page 2002-10-01 07:08:58 Re: [HACKERS] psqlODBC *nix Makefile (new 7.3 open item?)
Previous Message Alexey V. Borzov 2002-10-01 05:00:29 Re: [COMMITTERS] pgsql/contrib/rserv ApplySnapshot.in CleanLog. ...