Re: to_char WW - Mailing list pgsql-general

On Sun, 14 Jan 2001, gravity wrote:

> Karl,
>
> you implemented to_char if I'm not mistaken.
>
> May I ask how you got to the algorithm for WW (weeknumber of the year) and why?
>
> eg:
> znc=# select to_char(now(),'DD-MM-YYYY HH24:MI "week" WW');
>          to_char
> --------------------------
>  14-01-2001 01:11 week 03
> (1 row)
>
> and I was thinking that sun 14 jan 2001 would be weeknumber 2

 Not sure, see:

$ cal 1 2001

    January 2001
 S  M Tu  W Th  F  S
    1  2  3  4  5  6
 7  8  9 10 11 12 13
14 15 16 17 18 19 20
^^^^^^^
 Is it 3th or 2nd line? :-)

21 22 23 24 25 26 27
28 29 30 31

 But better explain of this FAQ's question (and for others in
 -generaly list):


First, in PG < 7.1 is not stuff of "week" too much correct, in 7.1 it's
better. Following is for 7.1...

 Docs:

W
- week of month (1-5) where first week start on the first day of the
  month

WW
- week number of year (1-53) where first week start on the first day
  of the year

IW
- ISO week number of year (The first Thursday of the new year is in
  week 1.)

 As you can see in 7.1 is IW that is more "human" than WW.

Examples:

select to_char('01-01-2001'::timestamp,
           'DD-MM-YYYY FMDay, "week:" WW, "iso-week:" IW');
                  to_char
-------------------------------------------
 01-01-2001 Monday, week: 01, iso-week: 01

 The year 2001 is not good example for show diff between ISO and WW,
2000 is better because year start after Thursday.

select to_char('01-01-2000'::timestamp, 'DD-MM-YYYY FMDay, "week:" WW,
"iso-week:" IW');
                   to_char
---------------------------------------------
 01-01-2000 Saturday, week: 01, iso-week: 52


test=# select to_char('07-01-2000'::timestamp, 'DD-MM-YYYY FMDay, "week:"
WW, "iso-week:" IW');
                  to_char
-------------------------------------------
 07-01-2000 Friday, week: 01, iso-week: 01
(1 row)


test=# select to_char('08-01-2000'::timestamp, 'DD-MM-YYYY FMDay, "week:"
WW, "iso-week:" IW');
                   to_char
---------------------------------------------
 08-01-2000 Saturday, week: 02, iso-week: 01


 Here (last two examples) is good transparent how week break to next week.

'WW' counts weeks directly as  ('day-of-year' -1) / 7 + 1

'IW' not counts weeks directly from 'day-of-year' but correct this value
      by first week of year, where first week of year is week with Thursday.

     - IW is like typical paper calendar (probably:-), but in some countries
     (like Czech Rep. where I live) people use weeks as Mon->Sun and not
     as more standard Sun->San. But ISO is clear for this.

                         Karel