From: | Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com>, Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | Haribabu kommi <haribabu(dot)kommi(at)huawei(dot)com>, Rushabh Lathia <rushabh(dot)lathia(at)enterprisedb(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: insert throw error when year field len > 4 for timestamptz datatype |
Date: | 2013-10-08 11:38:17 |
Message-ID: | CAGPqQf1uJp1veADioWKumFmsn-jB7vQ1TFUum76g7-oQVYzuXw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Atri Sharma | 2013-10-08 11:52:44 | Re: Re: custom hash-based COUNT(DISTINCT) aggregate - unexpectedly high memory consumption |
Previous Message | Haribabu kommi | 2013-10-08 11:13:04 | Re: Compression of full-page-writes |