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

Re: [HACKERS] Date conversion using day of week

From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
To: Brendan Jurd <direvus(at)gmail(dot)com>
Cc: Steve Crawford <scrawford(at)pinpointresearch(dot)com>, pgsql-general(at)postgresql(dot)org, marc(at)bloodnok(dot)com, hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Date conversion using day of week
Date: 2011-03-31 16:32:14
Message-ID: 4D94AC8E.3080806@gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
On 03/31/2011 08:27 AM, Brendan Jurd wrote:
> On 1 April 2011 02:00, Adrian Klaver<adrian(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)com

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2011-03-31 16:38:59
Subject: Re: Process local hint bit cache
Previous:From: Bruce MomjianDate: 2011-03-31 16:17:49
Subject: Bug in autovacuum.c?

pgsql-general by date

Next:From: Annamalai GurusamiDate: 2011-03-31 16:34:17
Subject: Merged Model for libpq
Previous:From: salah jubehDate: 2011-03-31 16:19:08
Subject: stack depth limit exceeded

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