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 yearstart on Thuesday (see 01-JAN-2002) and WW start weeks
each7 daysafter this first day of year.
If you need "human" week you must use IW (iso-week) that start everyMonday. I know there're countries where week start
onSunday, 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
for7.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
