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

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