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 <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
