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

Hi,

While working on something I come across this issue. Consider following
test:

postgres=# select version();
version

-----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4devel on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
(1 row)

postgres=# create table test ( a timestamptz);
CREATE TABLE

-- Date with year 1000
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1000 IST');
INSERT 0 1

-- Now try with year 10000 it will return error
postgres=# insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');
ERROR: invalid input syntax for type timestamp with time zone: "Sat Mar 11
23:58:48 10000 IST"
LINE 1: insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');

here error coming from timestamptz_in() -> datefields_to_timestamp() ->
DecodeDateTime() stack.

Looking more at the DecodeDateTime() function, here error coming while
trying
to Decode year field which is 10000 in the our test. For year field ftype is
DTK_NUMBER, and under DTK_NUMBER for this case if drop in to following
condition:

else if (flen > 4)
{
dterr = DecodeNumberField(flen, field[i], fmask,
&tmask, tm,
fsec, &is2digits);
if (dterr < 0)
return dterr;
}

because flen in out case flen is 5 (10000).

As per the comment above DecodeNumberField(), it interpret numeric string
as a
concatenated date or time field. So ideally we should be into
DecodeNumberField
function only with (fmask & DTK_DATE_M) == 0 or (fmask & DTK_TIME_M) == 0,
right ??

So, I tried the same and after that test working fine.

Another fix could be to modify DecodeNumberField() to only check for the
date and time when (fmask & DTK_DATE_M) == 0 and (fmask & DTK_TIME_M) == 0.
And if DecodeNumberField() returns error then call DecodeNumber() to check
the year possibility. But I didn't

Results after fix:

postgres=# select * from test;
a
------------------------------
1000-03-12 03:52:16+05:53:28
10000-03-12 03:28:48+05:30
(2 rows)

PFA patch and share your input/suggestions.
(With patch make check running fine without additional failures)

Regards,
Rushabh Lathia
www.EnterpriseDB.com

Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com> writes:
> PFA patch and share your input/suggestions.

I think this needs review. Please add it to the next commitfest.

regards, tom lane

On Thu, Aug 15, 2013 at 1:08 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com> writes:
> > PFA patch and share your input/suggestions.
>
> I think this needs review. Please add it to the next commitfest.
>

Done.

Here is latest patch with testcase added to regression.

>
> regards, tom lane
>

Regards,
Rushabh Lathia
www.EnterpriseDB.com

On 14 August 2013 Rushabh Lathia wrote:

>postgres=# create table test ( a timestamptz);
>CREATE TABLE

>-- Date with year 1000
>postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1000 IST');
>INSERT 0 1

>-- Now try with year 10000 it will return error
>postgres=# insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');
>ERROR: invalid input syntax for type timestamp with time zone: "Sat Mar 11 23:58:48 10000 IST"
>LINE 1: insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');

>here error coming from timestamptz_in() -> datefields_to_timestamp() ->
>DecodeDateTime() stack.

>Looking more at the DecodeDateTime() function, here error coming while trying
>to Decode year field which is 10000 in the our test. For year field ftype is
>DTK_NUMBER, and under DTK_NUMBER for this case if drop in to following condition:

>else if (flen > 4)
>{
>dterr = DecodeNumberField(flen, field[i], fmask,
> &tmask, tm,
> fsec, &is2digits);
>if (dterr < 0)
>return dterr;
>}

>because flen in out case flen is 5 (10000).

>As per the comment above DecodeNumberField(), it interpret numeric string as a
>concatenated date or time field. So ideally we should be into DecodeNumberField
>function only with (fmask & DTK_DATE_M) == 0 or (fmask & DTK_TIME_M) == 0,
>right ??

>So, I tried the same and after that test working fine.

>PFA patch and share your input/suggestions.

Patch applies cleanly to HEAD. As this patch tries to improve in inserting the date of the year value to be more than 4 in length.
But it didn't solve all the ways to insert the year field more than 4 in length. Please check the following test.

postgres=# insert into test values ('10001010 10:10:10 IST');
INSERT 0 1
postgres=# insert into test values ('100011010 10:10:10 IST');
ERROR: invalid input syntax for type timestamp with time zone: "100011010 10:10:10 IST" at character 26
STATEMENT: insert into test values ('100011010 10:10:10 IST');
ERROR: invalid input syntax for type timestamp with time zone: "100011010 10:10:10 IST"
LINE 1: insert into test values ('100011010 10:10:10 IST');
^

I feel it is better to provide the functionality of inserting year field more than 4 in length in all flows.

Regards,
Hari babu.

On Mon, Sep 16, 2013 at 7:22 PM, Haribabu kommi
<haribabu(dot)kommi(at)huawei(dot)com>wrote:

> *On *14 August 2013 Rushabh Lathia wrote:**
>
> ** **
>
> >postgres=# create table test ( a timestamptz);****
>
> >CREATE TABLE****
>
> ** **
>
> >-- Date with year 1000****
>
> >postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1000 IST');***
> *
>
> >INSERT 0 1****
>
> ** **
>
> >-- Now try with year 10000 it will return error****
>
> >postgres=# insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');**
> **
>
> >ERROR: invalid input syntax for type timestamp with time zone: "Sat Mar
> 11 23:58:48 10000 IST" ****
>
> >LINE 1: insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');****
>
> ** **
>
> >here error coming from timestamptz_in() -> datefields_to_timestamp() ->**
> **
>
> >DecodeDateTime() stack.****
>
> ** **
>
> >Looking more at the DecodeDateTime() function, here error coming while
> trying****
>
> >to Decode year field which is 10000 in the our test. For year field
> ftype is****
>
> >DTK_NUMBER, and under DTK_NUMBER for this case if drop in to following
> condition:****
>
> ** **
>
> >else if (flen > 4)****
>
> >{****
>
> >dterr = DecodeNumberField(flen, field[i], fmask,****
>
> > &tmask, tm,****
>
> > fsec, &is2digits);****
>
> >if (dterr < 0)****
>
> >return dterr;****
>
> >}****
>
> ** **
>
> >because flen in out case flen is 5 (10000).****
>
> ** **
>
> >As per the comment above DecodeNumberField(), it interpret numeric
> string as a****
>
> >concatenated date or time field. So ideally we should be into
> DecodeNumberField****
>
> >function only with (fmask & DTK_DATE_M) == 0 or (fmask & DTK_TIME_M) ==
> 0,****
>
> >right ??****
>
> ** **
>
> >So, I tried the same and after that test working fine.****
>
> ** **
>
> >PFA patch and share your input/suggestions.****
>
> ** **
>
> Patch applies cleanly to HEAD. As this patch tries to improve in inserting
> the date of the year value to be more than 4 in length.****
>
> But it didn’t solve all the ways to insert the year field more than 4 in
> length. Please check the following test.****
>
> ** **
>
> ** **
>
> postgres=# insert into test values ('10001010 10:10:10 IST');****
>
> INSERT 0 1****
>
> postgres=# insert into test values ('100011010 10:10:10 IST');****
>
> ERROR: invalid input syntax for type timestamp with time zone: "100011010
> 10:10:10 IST" at character 26****
>
> STATEMENT: insert into test values ('100011010 10:10:10 IST');****
>
> ERROR: invalid input syntax for type timestamp with time zone: "100011010
> 10:10:10 IST"****
>
> LINE 1: insert into test values ('100011010 10:10:10 IST');****
>
> ^****
>
> ** **
>
> I feel it is better to provide the functionality of inserting year field
> more than 4 in length in all flows.
>

+1. Nice catch.

Here is the latest version of patch which handles the functionality in all
flows.
Could you test it and share you comments.

Thanks,
Rushabh Lathia
www.EnterpriseDB.com

On Tue, 17 September 2013 14:33 Rushabh Lathia wrote:
>>On Mon, Sep 16, 2013 at 7:22 PM, Haribabu kommi <haribabu(dot)kommi(at)huawei(dot)com<mailto:haribabu(dot)kommi(at)huawei(dot)com>> wrote:
>>>On 14 August 2013 Rushabh Lathia wrote:
>>>postgres=# create table test ( a timestamptz);
>>>CREATE TABLE
>>>-- Date with year 1000
>>>postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1000 IST');
>>>INSERT 0 1
>>>-- Now try with year 10000 it will return error
>>>postgres=# insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');
>>>ERROR: invalid input syntax for type timestamp with time zone: "Sat Mar 11 23:58:48 10000 IST"
>>>LINE 1: insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');
>>Patch applies cleanly to HEAD. As this patch tries to improve in inserting the date of the year value to be more than 4 in length.
>>But it didn't solve all the ways to insert the year field more than 4 in length. Please check the following test.

>>postgres=# insert into test values ('10001010 10:10:10 IST');
>>INSERT 0 1
>>postgres=# insert into test values ('100011010 10:10:10 IST');
>>ERROR: invalid input syntax for type timestamp with time zone: "100011010 10:10:10 IST" at character 26
>>STATEMENT: insert into test values ('100011010 10:10:10 IST');
>>ERROR: invalid input syntax for type timestamp with time zone: "100011010 10:10:10 IST"
>>LINE 1: insert into test values ('100011010 10:10:10 IST');
^
>>I feel it is better to provide the functionality of inserting year field more than 4 in length in all flows.

>+1. Nice catch.

>Here is the latest version of patch which handles the functionality in all flows.
>Could you test it and share you comments.

I am getting some other failures with the updated patch also, please check the following tests.

select date 'January 8, 19990';
select timestamptz 'January 8, 199910 01:01:01 IST';
INSERT INTO TIMESTAMPTZ_TST VALUES(4, '10001 SAT 8 MAR 10:10:10 IST');

you can get the test scripts from regress test files of date.sql, timetz.sql, timestamp.sql and timestamptz.sql
and modify according to the patch for verification.

I feel changing the year value to accept the length (>4) is not simple.
So many places the year length crossing more than length 4 is not considered.
Search in the code with "yyyy" and correct all related paths.

Regards,
Hari babu.

Sorry for delay in reply.

On Tue, Sep 17, 2013 at 6:23 PM, Haribabu kommi
<haribabu(dot)kommi(at)huawei(dot)com>wrote:

> On Tue, 17 September 2013 14:33 Rushabh Lathia wrote:****
>
> >>On Mon, Sep 16, 2013 at 7:22 PM, Haribabu kommi <
> haribabu(dot)kommi(at)huawei(dot)com> wrote:****
>
> *>>>**On *14 August 2013 Rushabh Lathia wrote:****
>
> >>>postgres=# create table test ( a timestamptz);****
>
> >>>CREATE TABLE****
>
> >>>-- Date with year 1000****
>
> >>>postgres=# insert into test values ( 'Sat Mar 11 23:58:48 1000 IST');*
> ***
>
> >>>INSERT 0 1****
>
> >>>-- Now try with year 10000 it will return error****
>
> >>>postgres=# insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');
> ****
>
> >>>ERROR: invalid input syntax for type timestamp with time zone: "Sat
> Mar 11 23:58:48 10000 IST" ****
>
> >>>LINE 1: insert into test values ( 'Sat Mar 11 23:58:48 10000 IST');****
>
> >>Patch applies cleanly to HEAD. As this patch tries to improve in
> inserting the date of the year value to be more than 4 in length.****
>
> >>But it didn’t solve all the ways to insert the year field more than 4
> in length. Please check the following test.****
>
> ** **
>
> >>postgres=# insert into test values ('10001010 10:10:10 IST');****
>
> >>INSERT 0 1****
>
> >>postgres=# insert into test values ('100011010 10:10:10 IST');****
>
> >>ERROR: invalid input syntax for type timestamp with time zone:
> "100011010 10:10:10 IST" at character 26****
>
> >>STATEMENT: insert into test values ('100011010 10:10:10 IST');****
>
> >>ERROR: invalid input syntax for type timestamp with time zone:
> "100011010 10:10:10 IST"****
>
> >>LINE 1: insert into test values ('100011010 10:10:10 IST');****
>
> ^****
>
> >>I feel it is better to provide the functionality of inserting year
> field more than 4 in length in all flows.****
>
> ** **
>
> >+1. Nice catch.****
>
> ** **
>
> >Here is the latest version of patch which handles the functionality in
> all flows. ****
>
> >Could you test it and share you comments.****
>
> ** **
>
> I am getting some other failures with the updated patch also, please check
> the following tests.****
>
> ** **
>
> select date 'January 8, 19990';****
>
> select timestamptz 'January 8, 199910 01:01:01 IST';****
>
> INSERT INTO TIMESTAMPTZ_TST VALUES(4, '10001 SAT 8 MAR 10:10:10 IST');****
>
> ** **
>
> you can get the test scripts from regress test files of date.sql,
> timetz.sql, timestamp.sql and timestamptz.sql****
>
> and modify according to the patch for verification.****
>
> ** **
>
> I feel changing the year value to accept the length (>4) is not simple. **
> **
>
> So many places the year length crossing more than length 4 is not
> considered.****
>
> Search in the code with “yyyy” and correct all related paths.
>

Right, changing the year value to accept the length (>4) is not simple
because so
many places the year length crossing plus most of the please having
assumption
that it will be always <4.

Tried to fix issue more couple of places but I don't feeling like its
always going
to be safe to assume that we covered all path.

Still looking and wondering if we can do change in any simple place or
whether
we can find any other smarter way to fix the issue.

> ****
>
> ** **
>
> Regards,****
>
> Hari babu.****
>
> ** **
>

--
Rushabh Lathia

On 27 September 2013 15:04 Rushabh Lathia wrote:
>>On Tue, Sep 17, 2013 at 6:23 PM, Haribabu kommi <haribabu(dot)kommi(at)huawei(dot)com<mailto:haribabu(dot)kommi(at)huawei(dot)com>> wrote:
>>I feel changing the year value to accept the length (>4) is not simple.
>>So many places the year length crossing more than length 4 is not considered.
>>Search in the code with "yyyy" and correct all related paths.

>Right, changing the year value to accept the length (>4) is not simple because so
>many places the year length crossing plus most of the please having assumption
>that it will be always <4.

>Tried to fix issue more couple of places but I don't feeling like its always going
>to be safe to assume that we covered all path.

>Still looking and wondering if we can do change in any simple place or whether
>we can find any other smarter way to fix the issue.

If the changes are very high to deal all scenarios,
I feel it is better do it only in scenarios where the use cases needs it, until it is not confusing users.
The rest can be documented.
Any other opinions/suggestions welcome.

Regards,
Hari babu.

On Fri, Sep 27, 2013 at 10:42:17AM +0000, Haribabu kommi wrote:
> If the changes are very high to deal all scenarios,
>
> I feel it is better do it only in scenarios where the use cases needs it, until
> it is not confusing users.
>
> The rest can be documented.
>
> Any other opinions/suggestions welcome.

I have reviewed this patch and it is good. The problem is guessing if a
number with 5+ digits is YMD, HMS, or a year. I have created a modified
patch, attached, assumes a 5-digit number is a year, because YMD and HMS
require at least six digits, and used your date/time test to control the
other cases. I also added a few more regression tests.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

On Tue, Oct 1, 2013 at 7:52 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Fri, Sep 27, 2013 at 10:42:17AM +0000, Haribabu kommi wrote:
>> If the changes are very high to deal all scenarios,
>>
>> I feel it is better do it only in scenarios where the use cases needs it, until
>> it is not confusing users.
>>
>> The rest can be documented.
>>
>> Any other opinions/suggestions welcome.
>
> I have reviewed this patch and it is good. The problem is guessing if a
> number with 5+ digits is YMD, HMS, or a year. I have created a modified
> patch, attached, assumes a 5-digit number is a year, because YMD and HMS
> require at least six digits, and used your date/time test to control the
> other cases. I also added a few more regression tests.

In an ideal world the interpretation of the tokens wouldn't depend on
the order in which they appear. But we don't live in an ideal world,
so maybe this is fine.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Oct 2, 2013 at 11:00:30AM -0400, Robert Haas wrote:
> On Tue, Oct 1, 2013 at 7:52 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > On Fri, Sep 27, 2013 at 10:42:17AM +0000, Haribabu kommi wrote:
> >> If the changes are very high to deal all scenarios,
> >>
> >> I feel it is better do it only in scenarios where the use cases needs it, until
> >> it is not confusing users.
> >>
> >> The rest can be documented.
> >>
> >> Any other opinions/suggestions welcome.
> >
> > I have reviewed this patch and it is good. The problem is guessing if a
> > number with 5+ digits is YMD, HMS, or a year. I have created a modified
> > patch, attached, assumes a 5-digit number is a year, because YMD and HMS
> > require at least six digits, and used your date/time test to control the
> > other cases. I also added a few more regression tests.
>
> In an ideal world the interpretation of the tokens wouldn't depend on
> the order in which they appear. But we don't live in an ideal world,
> so maybe this is fine.

Yes, earlier in the thread the original patch poster questioned whether
he was going in the right direction, given the unusual hacks needed, but
such hacks are standard operating procedure for date/time stuff.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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.

Regards,
Rushabh

On Wed, Oct 2, 2013 at 9:34 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Wed, Oct 2, 2013 at 11:00:30AM -0400, Robert Haas wrote:
> > On Tue, Oct 1, 2013 at 7:52 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > > On Fri, Sep 27, 2013 at 10:42:17AM +0000, Haribabu kommi wrote:
> > >> If the changes are very high to deal all scenarios,
> > >>
> > >> I feel it is better do it only in scenarios where the use cases needs
> it, until
> > >> it is not confusing users.
> > >>
> > >> The rest can be documented.
> > >>
> > >> Any other opinions/suggestions welcome.
> > >
> > > I have reviewed this patch and it is good. The problem is guessing if
> a
> > > number with 5+ digits is YMD, HMS, or a year. I have created a
> modified
> > > patch, attached, assumes a 5-digit number is a year, because YMD and
> HMS
> > > require at least six digits, and used your date/time test to control
> the
> > > other cases. I also added a few more regression tests.
> >
> > In an ideal world the interpretation of the tokens wouldn't depend on
> > the order in which they appear. But we don't live in an ideal world,
> > so maybe this is fine.
>
> Yes, earlier in the thread the original patch poster questioned whether
> he was going in the right direction, given the unusual hacks needed, but
> such hacks are standard operating procedure for date/time stuff.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
>

--
Rushabh Lathia

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.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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.

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.

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

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

On Mon, Oct 7, 2013 at 12:41 AM, Rushabh Lathia
<rushabh(dot)lathia(at)gmail(dot)com> wrote:
> 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 ?

I'm not entirely convinced that this patch is heading in the right
direction. The thing is, it lets you use 5-digit years always and
longer years only in some contexts. So I'm not sure this is really
good enough for unambiguous date input. If you want that, you should
probably be using trusty YYYYYYYYYYY-MM-DD format. But if you don't
need that, then isn't a five-digit year most likely a typo? This
might be a case where throwing an error is actually better than trying
to make sense of the input.

I don't feel super-strongly about this, but I offer it as a question
for reflection.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Oct 8, 2013 at 1:34 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Mon, Oct 7, 2013 at 12:41 AM, Rushabh Lathia
> <rushabh(dot)lathia(at)gmail(dot)com> wrote:
> > 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 ?
>
> I'm not entirely convinced that this patch is heading in the right
> direction. The thing is, it lets you use 5-digit years always and
> longer years only in some contexts. So I'm not sure this is really
> good enough for unambiguous date input. If you want that, you should
> probably be using trusty YYYYYYYYYYY-MM-DD format. But if you don't
> need that, then isn't a five-digit year most likely a typo?

Do agree with you in certain extent.

But there are already ambiguity when it comes to postgres date module:

For example:
-- Doing select with year field > 4
edb=# select '10-10-22222'::timestamp;
timestamp
---------------------------
Thu Oct 10 00:00:00 22222
(1 row)

edb=# create table test ( a timestamp );
CREATE TABLE
-- When try to insert it throw an error
edb=# insert into test values ('Thu Oct 10 00:00:00 22222');
ERROR: invalid input syntax for type timestamp: "Thu Oct 10 00:00:00 22222"
LINE 1: insert into test values ('Thu Oct 10 00:00:00 22222');
^
Of course user can use the specific format and then this kind of date
can be used.

This
> might be a case where throwing an error is actually better than trying
> to make sense of the input.
>
> I don't feel super-strongly about this, but I offer it as a question
> for reflection.
>

At the same time I do agree fixing this kind of issue in postgres datetime
module
is bit difficult without some assumption. Personally I feel patch do add
some
value but not fully compatible with all kind of year field format.

Bruce,

Do you have any thought/suggestion ?

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

--
Rushabh Lathia

On Tue, Oct 8, 2013 at 05:08:17PM +0530, Rushabh Lathia wrote:
> This
> might be a case where throwing an error is actually better than trying
> to make sense of the input.
>
> I don't feel super-strongly about this, but I offer it as a question
> for reflection.
>
>
>
> At the same time I do agree fixing this kind of issue in postgres datetime
> module
> is bit difficult without some assumption. Personally I feel patch do add some
> value but not fully compatible with all kind of year field format.
>
> Bruce,
>
> Do you have any thought/suggestion ?

I think Robert is asking the right question: Is it better to accept
5-digit years, or throw an error? Doing anything new with 6-digit years
is going to break the much more common use of YMD or HMS.

The timestamp data type only supports values to year 294276, so the full
6-digit range isn't even supported. ('DATE' does go higher.)

The entire date/time processing allows imprecise input, so throwing an
error on clear 5-digit years seems wrong. Basically, we have gone down
the road of interpreting date/time input liberally, so throwing an error
on a clear 5-digit year seems odd.

On the other hand, this has never come up before because no one cared
about 5-digit years, so you could argue that 5-digit years require
precise specification, which would favor throwing an error.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

On Tue, Oct 8, 2013 at 09:05:37AM -0400, Bruce Momjian wrote:
> On Tue, Oct 8, 2013 at 05:08:17PM +0530, Rushabh Lathia wrote:
> > This
> > might be a case where throwing an error is actually better than trying
> > to make sense of the input.
> >
> > I don't feel super-strongly about this, but I offer it as a question
> > for reflection.
> >
> >
> >
> > At the same time I do agree fixing this kind of issue in postgres datetime
> > module
> > is bit difficult without some assumption. Personally I feel patch do add some
> > value but not fully compatible with all kind of year field format.
> >
> > Bruce,
> >
> > Do you have any thought/suggestion ?
>
> I think Robert is asking the right question: Is it better to accept
> 5-digit years, or throw an error? Doing anything new with 6-digit years
> is going to break the much more common use of YMD or HMS.
>
> The timestamp data type only supports values to year 294276, so the full
> 6-digit range isn't even supported. ('DATE' does go higher.)
>
> The entire date/time processing allows imprecise input, so throwing an
> error on clear 5-digit years seems wrong. Basically, we have gone down
> the road of interpreting date/time input liberally, so throwing an error
> on a clear 5-digit year seems odd.
>
> On the other hand, this has never come up before because no one cared
> about 5-digit years, so you could argue that 5-digit years require
> precise specification, which would favor throwing an error.

Patch applied to support 5+ digit years in non-ISO timestamp/date
strings, where appropriate.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +