Horton

Thread

  1. Date conversion using day of week

    Marc Munro <marc@bloodnok.com> — 2011-03-29T15:07:48Z

    I'm trying to validate a day of the week, and thought that to_date would
do the job for me.  But I found a case where it cannot tell the
difference between sunday and monday.  Is this a bug or intended
behaviour?

dev=# select to_date('2011-13-Mon', 'YYYY-IW-DY');
  to_date   
------------
 2011-03-28
(1 row)

dev=# select to_date('2011-13-Sun', 'YYYY-IW-DY');
  to_date   
------------
 2011-03-28
(1 row)

dev=# select to_date('2011-13-Tue', 'YYYY-IW-DY');
  to_date   
------------
 2011-03-29
(1 row)

This is on postgres 8.3.14.

__
Marc

  2. Re: Date conversion using day of week

    Adrian Klaver <adrian.klaver@gmail.com> — 2011-03-29T15:31:26Z

    On Tuesday, March 29, 2011 8:07:48 am Marc Munro wrote:
> I'm trying to validate a day of the week, and thought that to_date would
> do the job for me.  But I found a case where it cannot tell the
> difference between sunday and monday.  Is this a bug or intended
> behaviour?
> 
> dev=# select to_date('2011-13-Mon', 'YYYY-IW-DY');
>   to_date
> ------------
>  2011-03-28
> (1 row)
> 
> dev=# select to_date('2011-13-Sun', 'YYYY-IW-DY');
>   to_date
> ------------
>  2011-03-28
> (1 row)
> 
> dev=# select to_date('2011-13-Tue', 'YYYY-IW-DY');
>   to_date
> ------------
>  2011-03-29
> (1 row)
> 
> This is on postgres 8.3.14.
> 
> __
> Marc

Well in 9.0.3 this raises an error:

select to_date('2011-13-Sun', 'YYYY-IW-DY');
ERROR:  invalid combination of date conventions
HINT:  Do not mix Gregorian and ISO week date conventions in a formatting 
template

From the docs:
http://www.postgresql.org/docs/9.0/interactive/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 try this:

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

  3. Re: Date conversion using day of week

    Steve Crawford <scrawford@pinpointresearch.com> — 2011-03-29T15:33:59Z

    On 03/29/2011 08:07 AM, Marc Munro wrote:
> I'm trying to validate a day of the week, and thought that to_date would
> do the job for me.  But I found a case where it cannot tell the
> difference between sunday and monday.  Is this a bug or intended
> behaviour?
>
> dev=# select to_date('2011-13-Mon', 'YYYY-IW-DY');
>    to_date
> ------------
>   2011-03-28

Based on running the queries in 9.0, it's behavior that has been corrected:

select to_date('Mon1-13-Tue', 'YYYY-IW-DY');
ERROR:  invalid combination of date conventions
HINT:  Do not mix Gregorian and ISO week date conventions in a 
formatting template.

Cheers,
Steve

  4. Re: Date conversion using day of week

    Adrian Klaver <adrian.klaver@gmail.com> — 2011-03-29T15:50:56Z

    On Tuesday, March 29, 2011 8:33:59 am Steve Crawford wrote:
> On 03/29/2011 08:07 AM, Marc Munro wrote:
> > I'm trying to validate a day of the week, and thought that to_date would
> > do the job for me.  But I found a case where it cannot tell the
> > difference between sunday and monday.  Is this a bug or intended
> > behaviour?
> > 
> > dev=# select to_date('2011-13-Mon', 'YYYY-IW-DY');
> > 
> >    to_date
> > 
> > ------------
> > 
> >   2011-03-28
> 
> Based on running the queries in 9.0, it's behavior that has been corrected:
> 
> select to_date('Mon1-13-Tue', 'YYYY-IW-DY');
> ERROR:  invalid combination of date conventions
> HINT:  Do not mix Gregorian and ISO week date conventions in a
> formatting template.
> 
> Cheers,
> Steve


Yes and no:)

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-SUN', 'IYYY-IW-DY');
  to_date   
------------
 2011-03-28
(1 row)



-- 
Adrian Klaver
adrian.klaver@gmail.com

  5. Re: Date conversion using day of week

    Steve Crawford <scrawford@pinpointresearch.com> — 2011-03-29T16:02:52Z

    On 03/29/2011 08:50 AM, Adrian Klaver wrote:
> On Tuesday, March 29, 2011 8:33:59 am Steve Crawford wrote:
>> On 03/29/2011 08:07 AM, Marc Munro wrote:
>>> I'm trying to validate a day of the week, and thought that to_date would
>>> do the job for me.  But I found a case where it cannot tell the
>>> difference between sunday and monday.  Is this a bug or intended
>>> behaviour?
>>>
>>> dev=# select to_date('2011-13-Mon', 'YYYY-IW-DY');
>>>
>>>     to_date
>>>
>>> ------------
>>>
>>>    2011-03-28
>> Based on running the queries in 9.0, it's behavior that has been corrected:
>>
>> select to_date('Mon1-13-Tue', 'YYYY-IW-DY');
>> ERROR:  invalid combination of date conventions
>> HINT:  Do not mix Gregorian and ISO week date conventions in a
>> formatting template.
>>
>> Cheers,
>> Steve
>
> Yes and no:)
>
> 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-SUN', 'IYYY-IW-DY');
>    to_date
> ------------
>   2011-03-28
> (1 row)
>
>
>

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

  6. Re: Date conversion using day of week

    Adrian Klaver <adrian.klaver@gmail.com> — 2011-03-29T23:24:58Z

    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

  7. Re: Date conversion using day of week

    Steve Crawford <scrawford@pinpointresearch.com> — 2011-03-30T16:15:30Z

    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

  8. Re: Date conversion using day of week

    Adrian Klaver <adrian.klaver@gmail.com> — 2011-03-30T16:26:25Z

    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.klaver@gmail.com

  9. Re: [HACKERS] Date conversion using day of week

    Brendan Jurd <direvus@gmail.com> — 2011-03-31T03:39:25Z

    On 31 March 2011 03:15, Steve Crawford <scrawford@pinpointresearch.com> 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:


The relevant paragraphs in the docs are:

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

We *could* make the OP's query return the Sunday of ISO week 2011-13,
which would be properly written 2011-13-7, but I think the right move
here would be to throw the error for illegal mixture of format tokens.
 This is a trivial change -- just a matter of changing the from_date
type on the DAY, Day, day, DY, Dy, dy keys.

With the attached patch applied, this is what happens instead:

# select to_date('2011-13-SUN', 'IYYY-IW-DY');
ERROR:  invalid combination of date conventions
HINT:  Do not mix Gregorian and ISO week date conventions in a
formatting template.

If we wanted to make it "work", then I think the thing to do would be
to add a new set of formatting tokens IDY, IDAY etc.  I don't like the
idea of interpreting DY and co. differently depending on whether the
other tokens happen to be ISO week or Gregorian.

Cheers,
BJ

  10. Re: [HACKERS] Date conversion using day of week

    Adrian Klaver <adrian.klaver@gmail.com> — 2011-03-31T15:00:14Z

    On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
> On 31 March 2011 03:15, Steve Crawford <scrawford@pinpointresearch.com> 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:
> The relevant paragraphs in the docs are:
> 
> --
> 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.
> --
> 
> We *could* make the OP's query return the Sunday of ISO week 2011-13,
> which would be properly written 2011-13-7, but I think the right move
> here would be to throw the error for illegal mixture of format tokens.
>  This is a trivial change -- just a matter of changing the from_date
> type on the DAY, Day, day, DY, Dy, dy keys.
> 
> With the attached patch applied, this is what happens instead:
> 
> # select to_date('2011-13-SUN', 'IYYY-IW-DY');
> ERROR:  invalid combination of date conventions
> HINT:  Do not mix Gregorian and ISO week date conventions in a
> formatting template.
> 
> If we wanted to make it "work", then I think the thing to do would be
> to add a new set of formatting tokens IDY, IDAY etc.  I don't like the
> idea of interpreting DY and co. differently depending on whether the
> other tokens happen to be ISO week or Gregorian.

Just to play Devils advocate here, but why not? The day name is the same either 
way, it is the index that changes. I am not sure why that could not be context 
specific?

> 
> Cheers,
> BJ

-- 
Adrian Klaver
adrian.klaver@gmail.com

  11. Re: [HACKERS] Date conversion using day of week

    Brendan Jurd <direvus@gmail.com> — 2011-03-31T15:27:02Z

    On 1 April 2011 02:00, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
>> If we wanted to make it "work", then I think the thing to do would be
>> to add a new set of formatting tokens IDY, IDAY etc.  I don't like the
>> idea of interpreting DY and co. differently depending on whether the
>> other tokens happen to be ISO week or Gregorian.
>
> Just to play Devils advocate here, but why not? The day name is the same either
> way, it is the index that changes. I am not sure why that could not be context
> specific?
>

To be perfectly honest, it's mostly because I was hoping not to spend
very much more of my time in formatting.c.  Every time I go in there I
come out a little bit less sane.  I'm concerned that if I do anything
further to it, I might inadvertently summon Chattur'gha or something.
But since you went to the trouble of calling me on my laziness, let's
take a look at the problem.

At the time when the day-of-week token gets converted into a numeric
value and put into the TmFromChar.d field, the code has no knowledge
of whether the overall pattern is Gregorian or ISO (the DY field could
well be at the front of the pattern, for example).

Later on, in do_to_timestamp, the code expects the 'd' value to make
sense given the mode (it should be zero-based on Sunday for Gregorian,
or one-based on Monday for ISO).  That's all well and good *except* in
the totally bizarre case raised by the OP.

To resolve it, we could make TmFromChar.d always stored using the ISO
convention (because zero then has the useful property of meaning "not
set") and converted to the Gregorian convention as necessary in
do_to_timestamp.

Cheers,
BJ

  12. Re: [HACKERS] Date conversion using day of week

    Marc Munro <marc@bloodnok.com> — 2011-03-31T15:35:23Z

    On Thu, 2011-03-31 at 08:00 -0700, Adrian Klaver wrote:
> On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
> > On 31 March 2011 03:15, Steve Crawford <scrawford@pinpointresearch.com> wrote:
> > > On 03/29/2011 04:24 PM, Adrian Klaver wrote:
> > >> ...
> > >> Well the strange part is only fails for SUN:...
[. . .]
> > 
> > We *could* make the OP's query return the Sunday of ISO week 2011-13,
> > which would be properly written 2011-13-7, but I think the right move
> > here would be to throw the error for illegal mixture of format tokens.
> >  This is a trivial change -- just a matter of changing the from_date
> > type on the DAY, Day, day, DY, Dy, dy keys.
[. . .]
> Just to play Devils advocate here, but why not? The day name is the same either 
> way, it is the index that changes. I am not sure why that could not be context 
> specific?

Just to be clear, the reason I was mixing things in this way was that I
wanted to validate that the dayname being passed was valid for the
current locale, and I could find no easier way of doing it.  FTR, I have
now resorted to finding the given dayname in the results of this query:

select day, to_char(day, 'dy') as dayname, 
       extract('dow' from day) as dayno
  from (
    select current_date + n as day 
      from generate_series(0, 6) as n) d;

If there is an easier way of doing this, please let me know.  As far as
the postgres API goes, exposing a function that would validate a dayname
returning a day number would resolve all of this for considerably less
complexity.  Also throwing an error in the to_date function for
unexpectedly mixed input formats seems quite reasonable.

Thanks for your time and attention.  The commercial RDBMS vendors could
learn a lot about customer support from this forum.

__
Marc Munro

  13. Re: [HACKERS] Date conversion using day of week

    Brendan Jurd <direvus@gmail.com> — 2011-03-31T16:07:07Z

    On 1 April 2011 02:35, Marc Munro <marc@bloodnok.com> wrote:
> Just to be clear, the reason I was mixing things in this way was that I
> wanted to validate that the dayname being passed was valid for the
> current locale, and I could find no easier way of doing it.

Ah, I see.  In that case I think to_date would have disappointed you
even if IYYY-IW-DY did work, since the inputs do not appear to be
checked against the localised versions of the day names.  They are
only checked against the hard-coded English names.  to_date and
to_char are asymmetric in this sense -- localisation only happens on
the way out.

Cheers,
BJ

  14. Re: [HACKERS] Date conversion using day of week

    Adrian Klaver <adrian.klaver@gmail.com> — 2011-03-31T16:32:14Z

    On 03/31/2011 08:27 AM, Brendan Jurd wrote:
> On 1 April 2011 02:00, Adrian Klaver<adrian.klaver@gmail.com>  wrote:
>> On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
>>> If we wanted to make it "work", then I think the thing to do would be
>>> to add a new set of formatting tokens IDY, IDAY etc.  I don't like the
>>> idea of interpreting DY and co. differently depending on whether the
>>> other tokens happen to be ISO week or Gregorian.
>>
>> Just to play Devils advocate here, but why not? The day name is the same either
>> way, it is the index that changes. I am not sure why that could not be context
>> specific?
>>
>
> To be perfectly honest, it's mostly because I was hoping not to spend
> very much more of my time in formatting.c.  Every time I go in there I
> come out a little bit less sane.  I'm concerned that if I do anything
> further to it, I might inadvertently summon Chattur'gha or something.
> But since you went to the trouble of calling me on my laziness, let's
> take a look at the problem.

I understand, my foray into formatting.c has left an impression.

>
> At the time when the day-of-week token gets converted into a numeric
> value and put into the TmFromChar.d field, the code has no knowledge
> of whether the overall pattern is Gregorian or ISO (the DY field could
> well be at the front of the pattern, for example).

>
> Later on, in do_to_timestamp, the code expects the 'd' value to make
> sense given the mode (it should be zero-based on Sunday for Gregorian,
> or one-based on Monday for ISO).  That's all well and good *except* in
> the totally bizarre case raised by the OP.

Now I am confused the docs say:

D	day of the week, Sunday(1) to Saturday(7)
ID	ISO day of the week, Monday(1) to Sunday(7)

This would seem to say they both are one-based but differ on the day 
that is 1.

>
> To resolve it, we could make TmFromChar.d always stored using the ISO
> convention (because zero then has the useful property of meaning "not
> set") and converted to the Gregorian convention as necessary in
> do_to_timestamp.

Since I am in this deep might as well go deeper.

When I see the requirement:

IYYY-IW-IDY(proposed)
or
YYY-WW-DY
which is implied
GYYY-GWW-GDY

I see the constant being pulled out:

I YYY-W-DY
G YYY-W-DY

I know this presents backwards compatibility issues. Also that the data 
formatting functions are supposed to track Oracle behavior. It just 
seems a way to simplify the formatting process. Thanks for taking the 
time to explain the process.

>
> Cheers,
> BJ

-- 
Adrian Klaver
adrian.klaver@gmail.com

  15. Re: [HACKERS] Date conversion using day of week

    Brendan Jurd <direvus@gmail.com> — 2011-03-31T16:58:48Z

    On 1 April 2011 03:32, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> Now I am confused the docs say:
>
> D       day of the week, Sunday(1) to Saturday(7)
> ID      ISO day of the week, Monday(1) to Sunday(7)
>
> This would seem to say they both are one-based but differ on the day that is
> 1.

That's correct for the user-facing interpretation.  Internally,
however, Gregorian day-of-week is represented with Sunday = 0.  I
can't see any good reason in the code for why that should be so, but
it was like that when I found it, and until now I haven't had any
cause to mess with it.

My suggestion for moving forward basically still stands, though.  We'd
need to standardise the use of TmFromChar.d to either one of the
1-based conventions, and convert to the other one as required in
do_to_timestamp.  The Gregorian convention is probably the right
choice for the standard, even though it has the week starting on a
Sunday (ridiculous!) because it means less converting for the majority
of cases.

Cheers,
BJ

  16. Re: [HACKERS] Date conversion using day of week

    Steve Crawford <scrawford@pinpointresearch.com> — 2011-03-31T17:16:26Z

    On 03/31/2011 08:00 AM, Adrian Klaver wrote:
> On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
>> On 31 March 2011 03:15, Steve Crawford<scrawford@pinpointresearch.com>  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:
>> The relevant paragraphs in the docs are:
>>
>> --
>> 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.
>> --
>>
>> We *could* make the OP's query return the Sunday of ISO week 2011-13,
>> which would be properly written 2011-13-7, but I think the right move
>> here would be to throw the error for illegal mixture of format tokens.
>>   This is a trivial change -- just a matter of changing the from_date
>> type on the DAY, Day, day, DY, Dy, dy keys.
>>
>> With the attached patch applied, this is what happens instead:
>>
>> # select to_date('2011-13-SUN', 'IYYY-IW-DY');
>> ERROR:  invalid combination of date conventions
>> HINT:  Do not mix Gregorian and ISO week date conventions in a
>> formatting template.
>>
>> If we wanted to make it "work", then I think the thing to do would be
>> to add a new set of formatting tokens IDY, IDAY etc.  I don't like the
>> idea of interpreting DY and co. differently depending on whether the
>> other tokens happen to be ISO week or Gregorian.
> Just to play Devils advocate here, but why not? The day name is the same either
> way, it is the index that changes. I am not sure why that could not be context
> specific?
>

A week day represented as an int is ambiguous - as you mention, the 
index is necessary to decode to the correct day. "Sunday" is unambiguous 
so we could do something reasonable. But from everything I've read 
(though I didn't actually shell out 130CHF for a full 33-page copy of 
ISO8601:2004), the ISO *week* date format does not represent day-of-week 
as other than a numeric value so it would not really be an ISO8601 
formatted date and I would be tempted to thrown an error. However...

This whole discussion opens a #10 sized can o' worms. Admittedly, I 
don't have good knowledge of any SQL-mandated interpretations of an ISO 
date - but based on my reading of ISO formatting I see the following issues:

1. What we describe in the documentation as an ISO date is actually an 
ISO *week* date - a special purpose format included within ISO8601. 
2011-03-31 is also an ISO date as are 20110331, 20110331T013212 and 
20110331T21.3344298. "Fixing" this is probably as simple as a 
clarification in the documentation.

2. The ISO week-date format is defined as having the week-number 
prefaced by a "W" as in 2011-W03-7. From the ISO8601 FAQ page: "Week 
date is an alternative date representation used in many commercial and 
industrial applications. It is: YYYY-Www-D where YYYY is the Year in the 
Gregorian calendar, ww is the week of the year between 01 (the first 
week) and 52 or 53 (the last week), and D is the day in the week between 
1 (Monday) and 7 (Sunday). Example: 2003-W14-2 represents the second day 
of the fourteenth week of 2003." However PostgreSQL does *not* accept 
that as input even as specified as an "ISO" date:

select to_date('2003-W14-2', 'IYYY-IW-ID');
ERROR:  invalid value "W1" for "IW"
DETAIL:  Value must be an integer.

Fixing this would require both a coding change and a decision whether or 
not to throw an error on incorrectly formatted input.

3. ISO8601 requires zero-padding. PostgreSQL, however, does not complain 
if that padding is missing. The following should be "2011-04-2" 
(actually, "2011-W04-2" as noted above) but PostgreSQL accepts:

select to_date('2011-4-2', 'IYYY-IW-ID');
   to_date
------------
  2011-01-25

However in ISO dates the hyphens are supposed to only be for easier 
reading by humans. But if we just remove them:

select to_date('201142', 'IYYYIWID');
   to_date
------------
  2011-10-17

(Monday of the 42nd week).

Fix it and throw an error (and suffer the howls of anguish when backward 
compatibility is shattered) or tiptoe quietly away?

Of course to make things more complicated, the above also depends to 
some extent on whether you are looking at ISO 2014, ISO 2015, ISO 2711, 
ISO 3307, ISO 4031, ISO8601:1999, ISO8601:2000 or, finally, ISO8601:2004 
which supersedes all of the prior.

What I've concluded is that the root of the entire problem is providing 
ISO formatting options in pieces at all. The ISO date format has various 
requirements like ordering from largest temporal term to smallest, 
zero-padding, ""W" prefacing an ISO week, no "skipping" of temporal 
terms (201105 is May 2011, never the 5th of an unknown month), etc. all 
intended to make an ISO date string unambiguous. As such, it should only 
require a single format option saying "this is an ISO8601 date string" 
and mixing of ISO and Gregorian date formatting becomes impossible.

Cheers,
Steve

  17. Re: [HACKERS] Date conversion using day of week

    Brendan Jurd <direvus@gmail.com> — 2011-03-31T17:51:07Z

    On 1 April 2011 04:16, Steve Crawford <scrawford@pinpointresearch.com> wrote:
> This whole discussion opens a #10 sized can o' worms. Admittedly, I don't
> have good knowledge of any SQL-mandated interpretations of an ISO date - but
> based on my reading of ISO formatting I see the following issues:
>
> 1. What we describe in the documentation as an ISO date is actually an ISO
> *week* date - a special purpose format included within ISO8601. 2011-03-31
> is also an ISO date as are 20110331, 20110331T013212 and
> 20110331T21.3344298. "Fixing" this is probably as simple as a clarification
> in the documentation.

In the docs paragraph I quoted upthread, the full name "ISO week date"
is given.  Elsewhere the shorthand "ISO" or "ISO date" is used, in
contrast to the ordinary Gregorian style.  This is the only sense in
which we refer to "ISO" in the context of to_date, but I have no real
objection to expanding this to the full name "ISO week date"
everywhere it is mentioned, if people find the current usage
ambiguous.

> 2. The ISO week-date format is defined as having the week-number prefaced by
> a "W" as in 2011-W03-7.
...
> However PostgreSQL does *not* accept that as input even as
> specified as an "ISO" date:

It does, but you must use the somewhat awkward quoting notation to
indicate that the W is a literal character in the input string, not a
formatting character: 'IYYY-"W"IW-ID'

...
> What I've concluded is that the root of the entire problem is providing ISO
> formatting options in pieces at all. The ISO date format has various
> requirements like ordering from largest temporal term to smallest,
> zero-padding, ""W" prefacing an ISO week, no "skipping" of temporal terms
> (201105 is May 2011, never the 5th of an unknown month), etc. all intended
> to make an ISO date string unambiguous. As such, it should only require a
> single format option saying "this is an ISO8601 date string" and mixing of
> ISO and Gregorian date formatting becomes impossible.

I agree with your summary of the ISO standards.  Unfortunately,
to_date and its cohorts are not targeting ISO.  They are targeting
quasi-compatibility with some Oracle functions of the same name, I
suppose to make life easier for folks who are migrating from Oracle to
Postgres.  Any proposed reform of these (admittedly weird and kludgy)
functions is viewed through that lens, and usually rejected on those
grounds.  I've been down that road before.  There's not much point
having compatibility functions if they aren't, well, compatible.

In the big picture, to_date isn't meant to be the general entry point
for parsing dates.  If you wanted to make ISO8601 work as a syntax for
inputting date type literals vis. SELECT date '2011-W14-01', you might
have a better shot at getting that off the ground.

Cheers,
BJ

  18. Re: [HACKERS] Date conversion using day of week

    Steve Crawford <scrawford@pinpointresearch.com> — 2011-03-31T18:16:44Z

    On 03/31/2011 10:51 AM, Brendan Jurd wrote:
>
> I agree with your summary of the ISO standards.  Unfortunately,
> to_date and its cohorts are not targeting ISO.  They are targeting
> quasi-compatibility with some Oracle functions of the same name, I
> suppose to make life easier for folks who are migrating from Oracle to
> Postgres.  Any proposed reform of these (admittedly weird and kludgy)
> functions is viewed through that lens, and usually rejected on those
> grounds.  I've been down that road before.  There's not much point
> having compatibility functions if they aren't, well, compatible.
>
> In the big picture, to_date isn't meant to be the general entry point
> for parsing dates.  If you wanted to make ISO8601 work as a syntax for
> inputting date type literals vis. SELECT date '2011-W14-01', you might
> have a better shot at getting that off the ground.
>
Well, to return to the original issue, should we allow the day to be 
spelled out and fix it (as noted in this thread it is non-standard but 
also unambiguous and we already allow plenty of non-standard formats) or 
throw an error? For me personally, either would be fine. What isn't 
correct is the current behavior:

select to_date('2011-13-SUN', 'IYYY-IW-DY');
   to_date
------------
  2011-03-28

Cheers,
Steve

  19. Re: [HACKERS] Date conversion using day of week

    Brendan Jurd <direvus@gmail.com> — 2011-03-31T18:46:14Z

    On 1 April 2011 05:16, Steve Crawford <scrawford@pinpointresearch.com> wrote:
> Well, to return to the original issue, should we allow the day to be spelled
> out and fix it (as noted in this thread it is non-standard but also
> unambiguous and we already allow plenty of non-standard formats) or throw an
> error? For me personally, either would be fine. What isn't correct is the
> current behavior:

I started out thinking we should throw the error, but I am coming
around to the idea of fixing it.  I outlined how that might work in
reply to Adrian Klaver elsewhere in the thread [1].

Cheers,
BJ

[1] http://archives.postgresql.org/pgsql-hackers/2011-03/msg01906.php

  20. Re: [HACKERS] Date conversion using day of week

    Bruce Momjian <bruce@momjian.us> — 2012-09-01T21:11:23Z

    On Fri, Apr  1, 2011 at 02:27:02AM +1100, Brendan Jurd wrote:
> On 1 April 2011 02:00, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> > On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
> >> If we wanted to make it "work", then I think the thing to do would be
> >> to add a new set of formatting tokens IDY, IDAY etc.  I don't like the
> >> idea of interpreting DY and co. differently depending on whether the
> >> other tokens happen to be ISO week or Gregorian.
> >
> > Just to play Devils advocate here, but why not? The day name is the same either
> > way, it is the index that changes. I am not sure why that could not be context
> > specific?
> >
> 
> To be perfectly honest, it's mostly because I was hoping not to spend
> very much more of my time in formatting.c.  Every time I go in there I
> come out a little bit less sane.  I'm concerned that if I do anything
  -------------------------------

Agreed!

> further to it, I might inadvertently summon Chattur'gha or something.
> But since you went to the trouble of calling me on my laziness, let's
> take a look at the problem.
> 
> At the time when the day-of-week token gets converted into a numeric
> value and put into the TmFromChar.d field, the code has no knowledge
> of whether the overall pattern is Gregorian or ISO (the DY field could
> well be at the front of the pattern, for example).
> 
> Later on, in do_to_timestamp, the code expects the 'd' value to make
> sense given the mode (it should be zero-based on Sunday for Gregorian,
> or one-based on Monday for ISO).  That's all well and good *except* in
> the totally bizarre case raised by the OP.
> 
> To resolve it, we could make TmFromChar.d always stored using the ISO
> convention (because zero then has the useful property of meaning "not
> set") and converted to the Gregorian convention as necessary in
> do_to_timestamp.

I did quite a bit if study on this and have a fix in the attached patch.
Brendan above is correct about the cause of the problems.  Basically,
'd' was sometimes numbered 1-7 with Monday as week start, and 'd' was at
other times 0-6 with Sunday as start.  Plus, zero was used to designate
"not supplied" in ISO tests.  Obviously the number and the start value
both caused problems.

The attached patch fixes this by using Gregorian 1-7 (Sunday=7) format
throughout, allowing any mix of Gregorian and ISO week designations.  It
is converted to ISO (or Unix format 0-6, Sunday=0) as needed.

Sample output:

	test=> select to_date('2011-13-MON', 'IYYY-IW-DY');
	  to_date
	------------
	 2011-03-28
	(1 row)
	
	test=> select to_date('2011-13-SUN', 'IYYY-IW-DY');
	  to_date
	------------
	 2011-04-03
	(1 row)
	
	test=> select to_date('2011-13-SAT', 'IYYY-IW-DY');
	  to_date
	------------
	 2011-04-02
	(1 row)
	
	test=> select to_date('2011-13-1', 'IYYY-IW-ID');
	  to_date
	------------
	 2011-03-28
	(1 row)
	
	test=> select to_date('2011-13-7', 'IYYY-IW-ID');
	  to_date
	------------
	 2011-04-03
	(1 row)
	
	test=> select to_date('2011-13-0', 'IYYY-IW-ID');
	  to_date
	------------
	 2011-04-03
	(1 row)

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

  21. Re: [HACKERS] Date conversion using day of week

    Bruce Momjian <bruce@momjian.us> — 2012-09-01T21:11:59Z

    On Fri, Apr  1, 2011 at 03:58:48AM +1100, Brendan Jurd wrote:
> On 1 April 2011 03:32, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> > Now I am confused the docs say:
> >
> > D       day of the week, Sunday(1) to Saturday(7)
> > ID      ISO day of the week, Monday(1) to Sunday(7)
> >
> > This would seem to say they both are one-based but differ on the day that is
> > 1.
> 
> That's correct for the user-facing interpretation.  Internally,
> however, Gregorian day-of-week is represented with Sunday = 0.  I
> can't see any good reason in the code for why that should be so, but
> it was like that when I found it, and until now I haven't had any
> cause to mess with it.
> 
> My suggestion for moving forward basically still stands, though.  We'd
> need to standardise the use of TmFromChar.d to either one of the
> 1-based conventions, and convert to the other one as required in
> do_to_timestamp.  The Gregorian convention is probably the right
> choice for the standard, even though it has the week starting on a
> Sunday (ridiculous!) because it means less converting for the majority
> of cases.

Yes, I went in the direction you suggested with patch posted in recent
thread.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

  22. Re: [GENERAL] Date conversion using day of week

    Bruce Momjian <bruce@momjian.us> — 2012-09-01T21:14:39Z

    [Properly posted to hackers list]

On Fri, Apr  1, 2011 at 02:27:02AM +1100, Brendan Jurd wrote:
> On 1 April 2011 02:00, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> > On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
> >> If we wanted to make it "work", then I think the thing to do would be
> >> to add a new set of formatting tokens IDY, IDAY etc.  I don't like the
> >> idea of interpreting DY and co. differently depending on whether the
> >> other tokens happen to be ISO week or Gregorian.
> >
> > Just to play Devils advocate here, but why not? The day name is the same either
> > way, it is the index that changes. I am not sure why that could not be context
> > specific?
> >
> 
> To be perfectly honest, it's mostly because I was hoping not to spend
> very much more of my time in formatting.c.  Every time I go in there I
> come out a little bit less sane.  I'm concerned that if I do anything
  -------------------------------

Agreed!

> further to it, I might inadvertently summon Chattur'gha or something.
> But since you went to the trouble of calling me on my laziness, let's
> take a look at the problem.
> 
> At the time when the day-of-week token gets converted into a numeric
> value and put into the TmFromChar.d field, the code has no knowledge
> of whether the overall pattern is Gregorian or ISO (the DY field could
> well be at the front of the pattern, for example).
> 
> Later on, in do_to_timestamp, the code expects the 'd' value to make
> sense given the mode (it should be zero-based on Sunday for Gregorian,
> or one-based on Monday for ISO).  That's all well and good *except* in
> the totally bizarre case raised by the OP.
> 
> To resolve it, we could make TmFromChar.d always stored using the ISO
> convention (because zero then has the useful property of meaning "not
> set") and converted to the Gregorian convention as necessary in
> do_to_timestamp.

I did quite a bit if study on this and have a fix in the attached patch.
Brendan above is correct about the cause of the problems.  Basically,
'd' was sometimes numbered 1-7 with Monday as week start, and 'd' was at
other times 0-6 with Sunday as start.  Plus, zero was used to designate
"not supplied" in ISO tests.  Obviously the number and the start value
both caused problems.

The attached patch fixes this by using Gregorian 1-7 (Sunday=7) format
throughout, allowing any mix of Gregorian and ISO week designations.  It
is converted to ISO (or Unix format 0-6, Sunday=0) as needed.

Sample output:

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

        test=> select to_date('2011-13-SUN', 'IYYY-IW-DY');
          to_date
        ------------
         2011-04-03
        (1 row)

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

        test=> select to_date('2011-13-1', 'IYYY-IW-ID');
          to_date
        ------------
         2011-03-28
        (1 row)

        test=> select to_date('2011-13-7', 'IYYY-IW-ID');
          to_date
        ------------
         2011-04-03
        (1 row)

        test=> select to_date('2011-13-0', 'IYYY-IW-ID');
          to_date
        ------------
         2011-04-03
        (1 row)

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

  23. Re: [GENERAL] Date conversion using day of week

    Bruce Momjian <bruce@momjian.us> — 2012-09-04T02:53:02Z

    Patch applied.

---------------------------------------------------------------------------

On Sat, Sep  1, 2012 at 05:14:39PM -0400, Bruce Momjian wrote:
> [Properly posted to hackers list]
> 
> On Fri, Apr  1, 2011 at 02:27:02AM +1100, Brendan Jurd wrote:
> > On 1 April 2011 02:00, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> > > On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
> > >> If we wanted to make it "work", then I think the thing to do would be
> > >> to add a new set of formatting tokens IDY, IDAY etc.  I don't like the
> > >> idea of interpreting DY and co. differently depending on whether the
> > >> other tokens happen to be ISO week or Gregorian.
> > >
> > > Just to play Devils advocate here, but why not? The day name is the same either
> > > way, it is the index that changes. I am not sure why that could not be context
> > > specific?
> > >
> > 
> > To be perfectly honest, it's mostly because I was hoping not to spend
> > very much more of my time in formatting.c.  Every time I go in there I
> > come out a little bit less sane.  I'm concerned that if I do anything
>   -------------------------------
> 
> Agreed!
> 
> > further to it, I might inadvertently summon Chattur'gha or something.
> > But since you went to the trouble of calling me on my laziness, let's
> > take a look at the problem.
> > 
> > At the time when the day-of-week token gets converted into a numeric
> > value and put into the TmFromChar.d field, the code has no knowledge
> > of whether the overall pattern is Gregorian or ISO (the DY field could
> > well be at the front of the pattern, for example).
> > 
> > Later on, in do_to_timestamp, the code expects the 'd' value to make
> > sense given the mode (it should be zero-based on Sunday for Gregorian,
> > or one-based on Monday for ISO).  That's all well and good *except* in
> > the totally bizarre case raised by the OP.
> > 
> > To resolve it, we could make TmFromChar.d always stored using the ISO
> > convention (because zero then has the useful property of meaning "not
> > set") and converted to the Gregorian convention as necessary in
> > do_to_timestamp.
> 
> I did quite a bit if study on this and have a fix in the attached patch.
> Brendan above is correct about the cause of the problems.  Basically,
> 'd' was sometimes numbered 1-7 with Monday as week start, and 'd' was at
> other times 0-6 with Sunday as start.  Plus, zero was used to designate
> "not supplied" in ISO tests.  Obviously the number and the start value
> both caused problems.
> 
> The attached patch fixes this by using Gregorian 1-7 (Sunday=7) format
> throughout, allowing any mix of Gregorian and ISO week designations.  It
> is converted to ISO (or Unix format 0-6, Sunday=0) as needed.
> 
> Sample output:
> 
>         test=> select to_date('2011-13-MON', 'IYYY-IW-DY');
>           to_date
>         ------------
>          2011-03-28
>         (1 row)
> 
>         test=> select to_date('2011-13-SUN', 'IYYY-IW-DY');
>           to_date
>         ------------
>          2011-04-03
>         (1 row)
> 
>         test=> select to_date('2011-13-SAT', 'IYYY-IW-DY');
>           to_date
>         ------------
>          2011-04-02
>         (1 row)
> 
>         test=> select to_date('2011-13-1', 'IYYY-IW-ID');
>           to_date
>         ------------
>          2011-03-28
>         (1 row)
> 
>         test=> select to_date('2011-13-7', 'IYYY-IW-ID');
>           to_date
>         ------------
>          2011-04-03
>         (1 row)
> 
>         test=> select to_date('2011-13-0', 'IYYY-IW-ID');
>           to_date
>         ------------
>          2011-04-03
>         (1 row)
> 
> -- 
>   Bruce Momjian  <bruce@momjian.us>        http://momjian.us
>   EnterpriseDB                             http://enterprisedb.com
> 
>   + It's impossible for everything to be true. +

> diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
> new file mode 100644
> index 25af8a2..2aa6df1
> *** a/src/backend/utils/adt/formatting.c
> --- b/src/backend/utils/adt/formatting.c
> *************** typedef struct
> *** 412,418 ****
>   				mi,
>   				ss,
>   				ssss,
> ! 				d,
>   				dd,
>   				ddd,
>   				mm,
> --- 412,418 ----
>   				mi,
>   				ss,
>   				ssss,
> ! 				d,				/* stored as 1-7, Sunday = 1, 0 means missing */
>   				dd,
>   				ddd,
>   				mm,
> *************** DCH_from_char(FormatNode *node, char *in
> *** 2897,2902 ****
> --- 2897,2903 ----
>   				from_char_seq_search(&value, &s, days, ONE_UPPER,
>   									 MAX_DAY_LEN, n);
>   				from_char_set_int(&out->d, value, n);
> + 				out->d++;
>   				break;
>   			case DCH_DY:
>   			case DCH_Dy:
> *************** DCH_from_char(FormatNode *node, char *in
> *** 2904,2909 ****
> --- 2905,2911 ----
>   				from_char_seq_search(&value, &s, days, ONE_UPPER,
>   									 MAX_DY_LEN, n);
>   				from_char_set_int(&out->d, value, n);
> + 				out->d++;
>   				break;
>   			case DCH_DDD:
>   				from_char_parse_int(&out->ddd, &s, n);
> *************** DCH_from_char(FormatNode *node, char *in
> *** 2919,2929 ****
>   				break;
>   			case DCH_D:
>   				from_char_parse_int(&out->d, &s, n);
> - 				out->d--;
>   				s += SKIP_THth(n->suffix);
>   				break;
>   			case DCH_ID:
>   				from_char_parse_int_len(&out->d, &s, 1, n);
>   				s += SKIP_THth(n->suffix);
>   				break;
>   			case DCH_WW:
> --- 2921,2933 ----
>   				break;
>   			case DCH_D:
>   				from_char_parse_int(&out->d, &s, n);
>   				s += SKIP_THth(n->suffix);
>   				break;
>   			case DCH_ID:
>   				from_char_parse_int_len(&out->d, &s, 1, n);
> + 				/* Shift numbering to match Gregorian where Sunday = 1 */
> + 				if (++out->d > 7)
> + 					out->d = 1;
>   				s += SKIP_THth(n->suffix);
>   				break;
>   			case DCH_WW:
> *************** do_to_timestamp(text *date_txt, text *fm
> *** 3534,3540 ****
>   	if (tmfc.w)
>   		tmfc.dd = (tmfc.w - 1) * 7 + 1;
>   	if (tmfc.d)
> ! 		tm->tm_wday = tmfc.d;
>   	if (tmfc.dd)
>   		tm->tm_mday = tmfc.dd;
>   	if (tmfc.ddd)
> --- 3538,3544 ----
>   	if (tmfc.w)
>   		tmfc.dd = (tmfc.w - 1) * 7 + 1;
>   	if (tmfc.d)
> ! 		tm->tm_wday = tmfc.d - 1;	/* convert to native numbering */
>   	if (tmfc.dd)
>   		tm->tm_mday = tmfc.dd;
>   	if (tmfc.ddd)
> diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
> new file mode 100644
> index 2adc178..50ef897
> *** a/src/backend/utils/adt/timestamp.c
> --- b/src/backend/utils/adt/timestamp.c
> *************** isoweek2date(int woy, int *year, int *mo
> *** 3775,3792 ****
>   
>   /* isoweekdate2date()
>    *
> !  *	Convert an ISO 8601 week date (ISO year, ISO week and day of week) into a Gregorian date.
>    *	Populates year, mon, and mday with the correct Gregorian values.
>    *	year must be passed in as the ISO year.
>    */
>   void
> ! isoweekdate2date(int isoweek, int isowday, int *year, int *mon, int *mday)
>   {
>   	int			jday;
>   
>   	jday = isoweek2j(*year, isoweek);
> ! 	jday += isowday - 1;
> ! 
>   	j2date(jday, year, mon, mday);
>   }
>   
> --- 3775,3796 ----
>   
>   /* isoweekdate2date()
>    *
> !  *	Convert an ISO 8601 week date (ISO year, ISO week) into a Gregorian date.
> !  *	Gregorian day of week sent so weekday strings can be supplied.
>    *	Populates year, mon, and mday with the correct Gregorian values.
>    *	year must be passed in as the ISO year.
>    */
>   void
> ! isoweekdate2date(int isoweek, int wday, int *year, int *mon, int *mday)
>   {
>   	int			jday;
>   
>   	jday = isoweek2j(*year, isoweek);
> ! 	/* convert Gregorian week start (Sunday=1) to ISO week start (Monday=1) */
> ! 	if (wday > 1)
> ! 		jday += wday - 2;
> ! 	else
> ! 		jday += 6;
>   	j2date(jday, year, mon, mday);
>   }
>   
> diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
> new file mode 100644
> index 665e969..e7cdb41
> *** a/src/include/utils/timestamp.h
> --- b/src/include/utils/timestamp.h
> *************** extern int	timestamp_cmp_internal(Timest
> *** 236,242 ****
>   
>   extern int	isoweek2j(int year, int week);
>   extern void isoweek2date(int woy, int *year, int *mon, int *mday);
> ! extern void isoweekdate2date(int isoweek, int isowday, int *year, int *mon, int *mday);
>   extern int	date2isoweek(int year, int mon, int mday);
>   extern int	date2isoyear(int year, int mon, int mday);
>   extern int	date2isoyearday(int year, int mon, int mday);
> --- 236,242 ----
>   
>   extern int	isoweek2j(int year, int week);
>   extern void isoweek2date(int woy, int *year, int *mon, int *mday);
> ! extern void isoweekdate2date(int isoweek, int wday, int *year, int *mon, int *mday);
>   extern int	date2isoweek(int year, int mon, int mday);
>   extern int	date2isoyear(int year, int mon, int mday);
>   extern int	date2isoyearday(int year, int mon, int mday);

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


-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +