Skip site navigation (1) Skip section navigation (2)

Re: Date conversion using day of week

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, marc(at)bloodnok(dot)com, hackers(at)postgresql(dot)org
Subject: Re: Date conversion using day of week
Date: 2011-03-30 16:15:30
Message-ID: 4D935722.9020203@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2011-03-30 16:23:42
Subject: Re: Process local hint bit cache
Previous:From: Merlin MoncureDate: 2011-03-30 15:56:36
Subject: Re: Process local hint bit cache

pgsql-general by date

Next:From: Peter PanDate: 2011-03-30 16:23:51
Subject: Re: OSX Postgres PL/Perl Problem with Finance::Quote/Options
Previous:From: Paul GasparDate: 2011-03-30 16:02:07
Subject: -s does not work

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group