Search Postgresql Archives

Re: Date conversion using day of week

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 03/29/2011 04:24 PM, Adrian Klaver wrote:
...
Well the strange part is only fails for SUN:...
test(5432)aklaver=>select to_date('2011-13-SUN', 'IYYY-IW-DY');
   to_date
------------
  2011-03-28

...
You specified Sunday as the day but the date returned is a Monday. I would categorize that as a bug. (Hackers cc'd). Since Sunday is the last day of an ISO week, it should have returned 2011-04-03.

My first inclination without consulting source or morning coffee is that PostgreSQL is seeing Sunday as day zero. Note that while:
select to_date('2011-13-1', 'IYYY-IW-ID');
  to_date
------------
 2011-03-28

So does:
steve=# select to_date('2011-13-0', 'IYYY-IW-ID');
  to_date
------------
 2011-03-28

So something isn't right. All sorts of other stuff is allowed as well - I don't know if that's by design or not:

steve=# select to_date('2011-13--23', 'IYYY-IW-ID');
  to_date
------------
 2011-03-04


steve=# select to_date('2011-13-56', 'IYYY-IW-ID');
  to_date
------------
 2011-05-22


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

Monday
select to_date('2011-13-1', 'IYYY-IW-ID');...
We have to distinguish Gregorian and ISO days when represented as an integer since they define the start-of-week differently. Same with year. I don't think I've ever seen and ISO-week-date written as 2011-13-SUN but it *does* define a distinct date (which is not Monday). And even if PostgreSQL were updated to throw an error on that mix of formats it still leaves the problem of ISO day-of-week equal to zero.

Cheers,
Steve


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux