Thread: insert throw error when year field len > 4 for timestamptz datatype
Attachment
Rushabh Lathia <rushabh.lathia@gmail.com> writes:
> PFA patch and share your input/suggestions.
I think this needs review. Please add it to the next commitfest.
regards, tom lane
I think this needs review. Please add it to the next commitfest.
regards, tom lane
Rushabh Lathia
Attachment
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 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.
Attachment
On Tue, 17 September 2013 14:33 Rushabh Lathia wrote:
>>On Mon, Sep 16, 2013 at 7:22 PM, Haribabu kommi <haribabu.kommi@huawei.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.
On Tue, 17 September 2013 14:33 Rushabh Lathia wrote:
>>On Mon, Sep 16, 2013 at 7:22 PM, Haribabu kommi <haribabu.kommi@huawei.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.
Rushabh Lathia
On 27 September 2013 15:04 Rushabh Lathia wrote:
>>On Tue, Sep 17, 2013 at 6:23 PM, Haribabu kommi <haribabu.kommi@huawei.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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Attachment
On Tue, Oct 1, 2013 at 7:52 PM, Bruce Momjian <bruce@momjian.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@momjian.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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Yes, earlier in the thread the original patch poster questioned whetherOn Wed, Oct 2, 2013 at 11:00:30AM -0400, Robert Haas wrote:
> On Tue, Oct 1, 2013 at 7:52 PM, Bruce Momjian <bruce@momjian.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.
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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Rushabh Lathia
Attachment
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@momjian.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'; -- worksselect timestamptz '19991 01 08 01:01:01 IST'; -- worksselect timestamptz'1999100108 01:01:01 IST'; -- worksselect timestamptz '199910 01 08 01:01:01 IST'; -- Not workingselect timestamptz'January 8, 19991 01:01:01 IST'; -- worksselect timestamptz 'January 8, 199910 01:01:01 IST'; -- Not workingCREATETABLE TIMESTAMPTZ_TST (a int , b timestamptz); INSERT INTO TIMESTAMPTZ_TST VALUES(1, '100000312 23:58:48 IST');-- worksINSERT INTO TIMESTAMPTZ_TST VALUES(2, '10000 03 12 23:58:48 IST'); -- worksINSERT INTO TIMESTAMPTZ_TST VALUES(3,'1000000312 23:58:48 IST'); -- worksINSERT 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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Fri, Oct 4, 2013 at 10:19:38AM +0000, Haribabu kommi wrote:The general limitation we have is that while we know 5-digit numbers
>
> 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.
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@momjian.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.lathia@gmail.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 Mon, Oct 7, 2013 at 12:41 AM, Rushabh LathiaI'm not entirely convinced that this patch is heading in the right
<rushabh.lathia@gmail.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 ?
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.
At the same time I do agree fixing this kind of issue in postgres datetime module
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@momjian.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@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ Everyone has their own god. +