Postgresql likes Tuesday...

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 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@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 year
start on Thuesday (see 01-JAN-2002) and WW start weeks each 7 days
after this first day of year.

If you need "human" week you must use IW (iso-week) that start every
Monday.

I know there're countries where week start on Sunday, 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 for 7.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