Re: insert throw error when year field len > 4 for timestamptz datatype

On Fri, Oct 4, 2013 at 11:35 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Fri, Oct 4, 2013 at 10:19:38AM +0000, Haribabu kommi wrote:
> >
> > On 03 October 2013 19:30 Bruce Momjian wrote:
> > >On Thu, Oct 3, 2013 at 11:54:14AM +0530, Rushabh Lathia wrote:
> > >> Thanks Bruce.
> > >>
> > >> Yes for me main problem was to make assumption that a 5-digit number
> > >> is a year, as was bit worried about side effect of that assumption in
> > >> the date/time module. I did tested patch shared by you with various
> > >> test and so far it looks good to me.
> > >>
> > >> I would like reviewer to review/test the patch and share his comments.
> > >>
> > >> Attaching the git patch again with this mail.
> > >>
> > >> Assigning to Reviewer.
> >
> > >Oh, great. If everyone likes it I can apply it.
> >
> > With Year length of 6 digits has some inconsistency problem,
> > The tests are carried out on a default configuration.
>
> The general limitation we have is that while we know 5-digit numbers
> can't be YMD or HMS, we don't know that for 6-digit values, so we
> require that the string contain _a_ date and _a_ time specification
> before we consider a six-digit number as a year. I don't see how we can
> do any better than that. Your results below show that behavior. Do you
> have a suggestion for improvement?
>

Hmm right it has some inconsistency when year length is 6. But the patch
is based on assumption that 5-digit number is a year, because YMD and HMS
require at least six digits. Now Year with 6-digit number its getting
conflict with
YMD and HMS, that the reason its ending up with error. So with
patch approach
that's an expected behaviour for me.

I spent good amount of time on thinking how we can improve the behaviour, or
how can be change the assumption about the year field, YMD and HMS. At
current point of time it seems difficult to me because postgres date module
is tightly build with few assumption and changing that may lead to big
project.
Not sure but personally I feel that patch which was submitted earlier was
definitely good improvement.

Any other suggestion or thought for improvement ?

>
> ---------------------------------------------------------------------------
>
> > select timestamptz '199910108 01:01:01 IST'; -- works
> > select timestamptz '19991 01 08 01:01:01 IST'; -- works
> > select timestamptz '1999100108 01:01:01 IST'; -- works
> > select timestamptz '199910 01 08 01:01:01 IST'; -- Not working
> >
> > select timestamptz 'January 8, 19991 01:01:01 IST'; -- works
> > select timestamptz 'January 8, 199910 01:01:01 IST'; -- Not working
> >
> > CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
> > INSERT INTO TIMESTAMPTZ_TST VALUES(1, '100000312 23:58:48 IST'); --
> works
> > INSERT INTO TIMESTAMPTZ_TST VALUES(2, '10000 03 12 23:58:48 IST'); --
> works
> > INSERT INTO TIMESTAMPTZ_TST VALUES(3, '1000000312 23:58:48 IST'); --
> works
> > INSERT INTO TIMESTAMPTZ_TST VALUES(4, '100000 03 12 23:58:48 IST'); --
> Not working
> >
> > please correct me if anything wrong in the tests.
> >
> > Regards,
> > Hari babu.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
>

--
Rushabh Lathia