Re: Postgresql likes Tuesday...

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

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