Re: [GENERAL] Date conversion using day of week - Mailing list pgsql-hackers

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