Re: Date conversion using day of week - Mailing list pgsql-general

On Tuesday, March 29, 2011 9:02:52 am Steve Crawford wrote:

>
> But you changed it to specify an ISO year avoiding the mixed
> conventions. According to the 9.0 docs
> (http://www.postgresql.org/docs/9.0/static/functions-formatting.html):
>
>   "An ISO week date (as distinct from a Gregorian date) can be specified
> to to_timestamp and to_date in one of two ways:
>
>        Year, week, and weekday: for example to_date('2006-42-4',
> 'IYYY-IW-ID') returns the date 2006-10-19. If you omit the weekday it is
> assumed to be 1 (Monday).
>
>        Year and day of year: for example to_date('2006-291',
> 'IYYY-IDDD') also returns 2006-10-19.
>
> Attempting to construct a date using a mixture of ISO week and Gregorian
> date fields is nonsensical, and will cause an error. In the context of
> an ISO year, the concept of a "month" or "day of month" has no meaning.
> In the context of a Gregorian year, the ISO week has no meaning. Users
> should avoid mixing Gregorian and ISO date specifications. "
>
> So I guess the upshot is that 9.0 throws errors on mixed input, but the
> OP's issues can probably be resolved by explicitly specifying an ISO
> year in the formatting.
>
> Cheers,
> Steve

Well the strange part is only fails for SUN:

test(5432)aklaver=>select to_date('2011-13-MON', 'IYYY-IW-DY');
                                                           
  to_date
                                                           
------------
                                                           
 2011-03-28
                                                           
(1 row)
                                                           

                                                           
test(5432)aklaver=>select to_date('2011-13-TUE', 'IYYY-IW-DY');
  to_date
------------
 2011-03-29
(1 row)

test(5432)aklaver=>select to_date('2011-13-WED', 'IYYY-IW-DY');
  to_date
------------
 2011-03-30
(1 row)

test(5432)aklaver=>select to_date('2011-13-THU', 'IYYY-IW-DY');
  to_date
------------
 2011-03-31
(1 row)

test(5432)aklaver=>select to_date('2011-13-FRI', 'IYYY-IW-DY');
  to_date
------------
 2011-04-01
(1 row)

test(5432)aklaver=>select to_date('2011-13-SAT', 'IYYY-IW-DY');
  to_date
------------
 2011-04-02
(1 row)

test(5432)aklaver=>select to_date('2011-13-SUN', 'IYYY-IW-DY');
  to_date
------------
 2011-03-28

Agreed, maintaining ISO arguments across the board is the way to go:

Monday
select to_date('2011-13-1', 'IYYY-IW-ID');
  to_date
------------
 2011-03-28

Sunday
select to_date('2011-13-7', 'IYYY-IW-ID');
  to_date
------------
 2011-04-03


--
Adrian Klaver
adrian.klaver@gmail.com