Thread: Postgresql likes Tuesday...

Postgresql likes Tuesday...

From
Rod Taylor
Date:
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?

--  Rod Taylor



Re: Postgresql likes Tuesday...

From
Tom Lane
Date:
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


Re: Postgresql likes Tuesday...

From
Hannu Krosing
Date:
On Tue, 2002-10-01 at 03:31, Tom Lane wrote:
> 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:

There are at least 3 different ways to start week numbering:

1. from first week with any days in current year

2. from first full week in current year

3. from first week with thursday in current year

perhaps more...

I suspect it depends on locale which should be used.

---------------
Hannu




Re: Postgresql likes Tuesday...

From
Tom Lane
Date:
Hannu Krosing <hannu@tm.ee> writes:
> On Tue, 2002-10-01 at 03:31, Tom Lane wrote:
>> I notice that 2001-12-31 is considered part of the first week of 2002,
>> which is also pretty surprising:

> There are at least 3 different ways to start week numbering:
> ...
> I suspect it depends on locale which should be used.

Perhaps.  But I think there are two distinct issues here.  One is
whether EXTRACT(week) is assigning reasonable week numbers to dates;
this depends on your convention for which day is the first of a week
as well as your convention for the first week of a year (both possibly
should depend on locale as Hannu suggests).  The other issue is what
to_date(...,'WWYYYY') should do to produce a date representing a week
number.  Shouldn't it always produce the first date of that week?
If not, what other conventions make sense?
        regards, tom lane


Re: Postgresql likes Tuesday...

From
Hannu Krosing
Date:
On Tue, 2002-10-01 at 03:49, Tom Lane wrote:
> Hannu Krosing <hannu@tm.ee> writes:
> > On Tue, 2002-10-01 at 03:31, Tom Lane wrote:
> >> I notice that 2001-12-31 is considered part of the first week of 2002,
> >> which is also pretty surprising:
> 
> > There are at least 3 different ways to start week numbering:
> > ...
> > I suspect it depends on locale which should be used.
> 
> Perhaps.  But I think there are two distinct issues here.  One is
> whether EXTRACT(week) is assigning reasonable week numbers to dates;
> this depends on your convention for which day is the first of a week
> as well as your convention for the first week of a year (both possibly
> should depend on locale as Hannu suggests).  The other issue is what
> to_date(...,'WWYYYY') should do to produce a date representing a week
> number.  Shouldn't it always produce the first date of that week?

Producing middle-of-the week date is least likely to get a date in last
year.

Also should  

select to_timestamp('01102002','DDMMYYYY');

also produce midday (12:00) for time, instead of current 00:00 ?

-----------------
Hannu




Re: Postgresql likes Tuesday...

From
"Clark C. Evans"
Date:
On Mon, Sep 30, 2002 at 06:49:34PM -0400, Tom Lane wrote:
| The other issue is what
| to_date(...,'WWYYYY') should do to produce a date representing a week
| number.  Shouldn't it always produce the first date of that week?
| If not, what other conventions make sense?

IMHO, it should choose the "Week Ending" date.  This is
usually what all of the companies that I've worked with
want to see for the "day" column.  For example, the 
defect^H^H^H^H^H^H quality reports at Ford Motor in 1993
used a Predo of part by defect by week-ending.  Where
week ending date was the Sunday following the work 
week (monday-sunday).   In various project data in
companies that I've worked with before and after 1993
I've yet to see a "weekly" report that didn't give
the week ending... alhtough some did use Friday or
Saturday for the week ending.

One hickup with this choice is that you'd probably 
want the time portion to be 23:59:59.999 so that it
includes everything up to the end of the day.  Hmm.

Clark


Re: Postgresql likes Tuesday...

From
Karel Zak
Date:
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 ofweek"    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


Re: Postgresql likes Tuesday...

From
Karel Zak
Date:
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


Re: Postgresql likes Tuesday...

From
Tom Lane
Date:
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


Re: Postgresql likes Tuesday...

From
"Tim Knowles"
Date:
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