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

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?

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

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