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

Re: Date conversion using day of week

From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
To: Steve Crawford <scrawford(at)pinpointresearch(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:26:25
Message-ID: 4D9359B1.9080205@gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
On 03/30/2011 09:15 AM, Steve Crawford wrote:
> 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:

I started going through the source (formatting.c,timestamp.c), got as 
far as the Julian date functions before the brain imploded and I had to 
take a break:) I would agree it has to do with the difference in the 
week rotating around either Sunday or Monday.

> 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:

Well I can see how this is possible and indeed likely. The permutations 
of all the possible date/time representations is immense. It just 
emphasizes that when dealing with time consistency is good.

>
> 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
>
>

>
> Cheers,
> Steve
>


-- 
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

In response to

pgsql-hackers by date

Next:From: Dimitri FontaineDate: 2011-03-30 16:29:32
Subject: Re: Another swing at JSON
Previous:From: Heikki LinnakangasDate: 2011-03-30 16:23:42
Subject: Re: Process local hint bit cache

pgsql-general by date

Next:From: Adrian KlaverDate: 2011-03-30 16:34:42
Subject: Re: -s does not work
Previous:From: Peter PanDate: 2011-03-30 16:23:51
Subject: Re: OSX Postgres PL/Perl Problem with Finance::Quote/Options

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