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
Attachments:
timestamptz_fix.patchapplication/octet-stream; name=timestamptz_fix.patchDownload
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 7a08b92..a8370d6 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -1161,7 +1161,11 @@ DecodeDateTime(char **field, int *ftype, int nf,
if (dterr < 0)
return dterr;
}
- else if (flen > 4)
+ /*
+ * should call DecodeNumberField only when anyone Date or
+ * Time field is not yet found.
+ */
+ else if (flen > 4 && (!(fmask & DTK_DATE_M) || !(fmask & DTK_TIME_M)))
{
dterr = DecodeNumberField(flen, field[i], fmask,
&tmask, tm,
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Aug 15, 2013 at 1:08 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
Done.
Here is latest patch with testcase added to regression.
regards, tom lane
Regards,
Rushabh Lathia
www.EnterpriseDB.com
Attachments:
timestamptz_fix_with_testcase.patchapplication/octet-stream; name=timestamptz_fix_with_testcase.patchDownload
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 7a08b92..a8370d6 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -1161,7 +1161,11 @@ DecodeDateTime(char **field, int *ftype, int nf,
if (dterr < 0)
return dterr;
}
- else if (flen > 4)
+ /*
+ * should call DecodeNumberField only when anyone Date or
+ * Time field is not yet found.
+ */
+ else if (flen > 4 && (!(fmask & DTK_DATE_M) || !(fmask & DTK_TIME_M)))
{
dterr = DecodeNumberField(flen, field[i], fmask,
&tmask, tm,
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 6581b5e..9259f0b 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -1675,3 +1675,19 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
| 2001 1 1 1 1 1 1
(66 rows)
+CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
+-- Test year field value with len > 4
+INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 11 23:58:48 1000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(2, 'Sat Mar 11 23:58:48 10000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(3, 'Sat Mar 11 23:58:48 100000 IST');
+--Verify data
+SELECT * FROM TIMESTAMPTZ_TST;
+ a | b
+---+--------------------------------
+ 1 | Tue Mar 11 13:58:48 1000 PST
+ 2 | Sat Mar 11 13:58:48 10000 PST
+ 3 | Sat Mar 11 13:58:48 100000 PST
+(3 rows)
+
+--Cleanup
+DROP TABLE TIMESTAMPTZ_TST;
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 863b286..b70793c 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -240,3 +240,14 @@ SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID')
SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
FROM TIMESTAMPTZ_TBL;
+
+CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
+
+-- Test year field value with len > 4
+INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 11 23:58:48 1000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(2, 'Sat Mar 11 23:58:48 10000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(3, 'Sat Mar 11 23:58:48 100000 IST');
+--Verify data
+SELECT * FROM TIMESTAMPTZ_TST;
+--Cleanup
+DROP TABLE TIMESTAMPTZ_TST;
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.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');****
** **
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
Attachments:
timestamptz_fix_with_testcase_v2.patchapplication/octet-stream; name=timestamptz_fix_with_testcase_v2.patchDownload
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 7a08b92..a508ef9 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -1161,7 +1161,11 @@ DecodeDateTime(char **field, int *ftype, int nf,
if (dterr < 0)
return dterr;
}
- else if (flen > 4)
+ /*
+ * should call DecodeNumberField only when anyone Date or
+ * Time field is not yet found.
+ */
+ else if (flen > 4 && (!(fmask & DTK_DATE_M) || !(fmask & DTK_TIME_M)))
{
dterr = DecodeNumberField(flen, field[i], fmask,
&tmask, tm,
@@ -2647,29 +2651,20 @@ DecodeNumberField(int len, char *str, int fmask,
/* No decimal point and no complete date yet? */
else if ((fmask & DTK_DATE_M) != DTK_DATE_M)
{
- /* yyyymmdd? */
- if (len == 8)
- {
- *tmask = DTK_DATE_M;
-
- tm->tm_mday = atoi(str + 6);
- *(str + 6) = '\0';
- tm->tm_mon = atoi(str + 4);
- *(str + 4) = '\0';
- tm->tm_year = atoi(str + 0);
-
- return DTK_DATE;
- }
- /* yymmdd? */
- else if (len == 6)
+ if (len >= 6)
{
*tmask = DTK_DATE_M;
- tm->tm_mday = atoi(str + 4);
- *(str + 4) = '\0';
- tm->tm_mon = atoi(str + 2);
- *(str + 2) = '\0';
- tm->tm_year = atoi(str + 0);
- *is2digits = TRUE;
+ /*
+ * Start from end and consider first 2 as Day next 2 as Month and rest
+ * as Year field.
+ */
+ tm->tm_mday = atoi(str + (len-2));
+ *(str + (len-2)) = '\0';
+ tm->tm_mon = atoi(str + (len-4));
+ *(str + (len-4)) = '\0';
+ tm->tm_year = atoi(str+0);
+ if ((len-4) == 2)
+ *is2digits = TRUE;
return DTK_DATE;
}
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 6581b5e..84361df 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -1675,3 +1675,21 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
| 2001 1 1 1 1 1 1
(66 rows)
+CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
+-- Test year field value with len > 4
+INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 11 23:58:48 1000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(2, 'Sat Mar 11 23:58:48 10000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(3, 'Sat Mar 11 23:58:48 100000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(4, '100011010 10:10:10 IST');
+--Verify data
+SELECT * FROM TIMESTAMPTZ_TST ORDER BY a;
+ a | b
+---+--------------------------------
+ 1 | Tue Mar 11 13:58:48 1000 PST
+ 2 | Sat Mar 11 13:58:48 10000 PST
+ 3 | Sat Mar 11 13:58:48 100000 PST
+ 4 | Wed Oct 10 01:10:10 10001 PDT
+(4 rows)
+
+--Cleanup
+DROP TABLE TIMESTAMPTZ_TST;
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 863b286..27f96dd 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -240,3 +240,15 @@ SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID')
SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
FROM TIMESTAMPTZ_TBL;
+
+CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
+
+-- Test year field value with len > 4
+INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 11 23:58:48 1000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(2, 'Sat Mar 11 23:58:48 10000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(3, 'Sat Mar 11 23:58:48 100000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(4, '100011010 10:10:10 IST');
+--Verify data
+SELECT * FROM TIMESTAMPTZ_TST ORDER BY a;
+--Cleanup
+DROP TABLE TIMESTAMPTZ_TST;
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<mailto: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.
Sorry for delay in reply.
On Tue, Sep 17, 2013 at 6:23 PM, Haribabu kommi
<haribabu.kommi@huawei.com>wrote:
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.
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.kommi@huawei.com<mailto: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. +
Attachments:
timestamp.difftext/x-diff; charset=us-asciiDownload
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
new file mode 100644
index f39353f..48bf3db
*** a/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
*************** DecodeDateTime(char **field, int *ftype,
*** 1161,1167 ****
if (dterr < 0)
return dterr;
}
! else if (flen > 4)
{
dterr = DecodeNumberField(flen, field[i], fmask,
&tmask, tm,
--- 1161,1177 ----
if (dterr < 0)
return dterr;
}
! /*
! * Is this a YMD or HMS specification, or a year number?
! * YMD and HMS are required to be six digits or more, so
! * if it is 5 digits, it is a year. If it is six or more
! * more digits, we assume it is YMD or HMS unless no date
! * and no time values have been specified. This forces
! * 6+ digit years to be at the end of the string, or to use
! * the ISO date specification.
! */
! else if (flen >= 6 && (!(fmask & DTK_DATE_M) ||
! !(fmask & DTK_TIME_M)))
{
dterr = DecodeNumberField(flen, field[i], fmask,
&tmask, tm,
*************** DecodeNumberField(int len, char *str, in
*** 2647,2675 ****
/* No decimal point and no complete date yet? */
else if ((fmask & DTK_DATE_M) != DTK_DATE_M)
{
! /* yyyymmdd? */
! if (len == 8)
! {
! *tmask = DTK_DATE_M;
!
! tm->tm_mday = atoi(str + 6);
! *(str + 6) = '\0';
! tm->tm_mon = atoi(str + 4);
! *(str + 4) = '\0';
! tm->tm_year = atoi(str + 0);
!
! return DTK_DATE;
! }
! /* yymmdd? */
! else if (len == 6)
{
*tmask = DTK_DATE_M;
! tm->tm_mday = atoi(str + 4);
! *(str + 4) = '\0';
! tm->tm_mon = atoi(str + 2);
! *(str + 2) = '\0';
! tm->tm_year = atoi(str + 0);
! *is2digits = TRUE;
return DTK_DATE;
}
--- 2657,2676 ----
/* No decimal point and no complete date yet? */
else if ((fmask & DTK_DATE_M) != DTK_DATE_M)
{
! if (len >= 6)
{
*tmask = DTK_DATE_M;
! /*
! * Start from end and consider first 2 as Day, next 2 as Month,
! * and the rest as Year.
! */
! tm->tm_mday = atoi(str + (len - 2));
! *(str + (len - 2)) = '\0';
! tm->tm_mon = atoi(str + (len - 4));
! *(str + (len - 4)) = '\0';
! tm->tm_year = atoi(str);
! if ((len - 4) == 2)
! *is2digits = TRUE;
return DTK_DATE;
}
*************** DecodeNumberField(int len, char *str, in
*** 2686,2692 ****
*(str + 4) = '\0';
tm->tm_min = atoi(str + 2);
*(str + 2) = '\0';
! tm->tm_hour = atoi(str + 0);
return DTK_TIME;
}
--- 2687,2693 ----
*(str + 4) = '\0';
tm->tm_min = atoi(str + 2);
*(str + 2) = '\0';
! tm->tm_hour = atoi(str);
return DTK_TIME;
}
*************** DecodeNumberField(int len, char *str, in
*** 2697,2703 ****
tm->tm_sec = 0;
tm->tm_min = atoi(str + 2);
*(str + 2) = '\0';
! tm->tm_hour = atoi(str + 0);
return DTK_TIME;
}
--- 2698,2704 ----
tm->tm_sec = 0;
tm->tm_min = atoi(str + 2);
*(str + 2) = '\0';
! tm->tm_hour = atoi(str);
return DTK_TIME;
}
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
new file mode 100644
index 6581b5e..9f4f7a4
*** a/src/test/regress/expected/timestamptz.out
--- b/src/test/regress/expected/timestamptz.out
*************** SELECT '' AS to_char_11, to_char(d1, 'FM
*** 1675,1677 ****
--- 1675,1699 ----
| 2001 1 1 1 1 1 1
(66 rows)
+ CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
+ -- Test year field value with len > 4
+ INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 12 23:58:48 1000 IST');
+ INSERT INTO TIMESTAMPTZ_TST VALUES(2, 'Sat Mar 12 23:58:48 10000 IST');
+ INSERT INTO TIMESTAMPTZ_TST VALUES(3, 'Sat Mar 12 23:58:48 100000 IST');
+ INSERT INTO TIMESTAMPTZ_TST VALUES(3, '10000 Mar 12 23:58:48 IST');
+ INSERT INTO TIMESTAMPTZ_TST VALUES(4, '100000312 23:58:48 IST');
+ INSERT INTO TIMESTAMPTZ_TST VALUES(4, '1000000312 23:58:48 IST');
+ --Verify data
+ SELECT * FROM TIMESTAMPTZ_TST ORDER BY a;
+ a | b
+ ---+--------------------------------
+ 1 | Wed Mar 12 13:58:48 1000 PST
+ 2 | Sun Mar 12 14:58:48 10000 PDT
+ 3 | Sun Mar 12 14:58:48 100000 PDT
+ 3 | Sun Mar 12 14:58:48 10000 PDT
+ 4 | Sun Mar 12 14:58:48 10000 PDT
+ 4 | Sun Mar 12 14:58:48 100000 PDT
+ (6 rows)
+
+ --Cleanup
+ DROP TABLE TIMESTAMPTZ_TST;
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
new file mode 100644
index 863b286..4eef62e
*** a/src/test/regress/sql/timestamptz.sql
--- b/src/test/regress/sql/timestamptz.sql
*************** SELECT '' AS to_char_10, to_char(d1, 'IY
*** 240,242 ****
--- 240,256 ----
SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
FROM TIMESTAMPTZ_TBL;
+
+ CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
+
+ -- Test year field value with len > 4
+ INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 12 23:58:48 1000 IST');
+ INSERT INTO TIMESTAMPTZ_TST VALUES(2, 'Sat Mar 12 23:58:48 10000 IST');
+ INSERT INTO TIMESTAMPTZ_TST VALUES(3, 'Sat Mar 12 23:58:48 100000 IST');
+ INSERT INTO TIMESTAMPTZ_TST VALUES(3, '10000 Mar 12 23:58:48 IST');
+ INSERT INTO TIMESTAMPTZ_TST VALUES(4, '100000312 23:58:48 IST');
+ INSERT INTO TIMESTAMPTZ_TST VALUES(4, '1000000312 23:58:48 IST');
+ --Verify data
+ SELECT * FROM TIMESTAMPTZ_TST ORDER BY a;
+ --Cleanup
+ DROP TABLE TIMESTAMPTZ_TST;
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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@momjian.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@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. +
--
Rushabh Lathia
Attachments:
timestamptz_fix_with_testcase_v3.patchapplication/octet-stream; name=timestamptz_fix_with_testcase_v3.patchDownload
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index f39353f..48bf3db 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -1161,7 +1161,17 @@ DecodeDateTime(char **field, int *ftype, int nf,
if (dterr < 0)
return dterr;
}
- else if (flen > 4)
+ /*
+ * Is this a YMD or HMS specification, or a year number?
+ * YMD and HMS are required to be six digits or more, so
+ * if it is 5 digits, it is a year. If it is six or more
+ * more digits, we assume it is YMD or HMS unless no date
+ * and no time values have been specified. This forces
+ * 6+ digit years to be at the end of the string, or to use
+ * the ISO date specification.
+ */
+ else if (flen >= 6 && (!(fmask & DTK_DATE_M) ||
+ !(fmask & DTK_TIME_M)))
{
dterr = DecodeNumberField(flen, field[i], fmask,
&tmask, tm,
@@ -2647,29 +2657,20 @@ DecodeNumberField(int len, char *str, int fmask,
/* No decimal point and no complete date yet? */
else if ((fmask & DTK_DATE_M) != DTK_DATE_M)
{
- /* yyyymmdd? */
- if (len == 8)
- {
- *tmask = DTK_DATE_M;
-
- tm->tm_mday = atoi(str + 6);
- *(str + 6) = '\0';
- tm->tm_mon = atoi(str + 4);
- *(str + 4) = '\0';
- tm->tm_year = atoi(str + 0);
-
- return DTK_DATE;
- }
- /* yymmdd? */
- else if (len == 6)
+ if (len >= 6)
{
*tmask = DTK_DATE_M;
- tm->tm_mday = atoi(str + 4);
- *(str + 4) = '\0';
- tm->tm_mon = atoi(str + 2);
- *(str + 2) = '\0';
- tm->tm_year = atoi(str + 0);
- *is2digits = TRUE;
+ /*
+ * Start from end and consider first 2 as Day, next 2 as Month,
+ * and the rest as Year.
+ */
+ tm->tm_mday = atoi(str + (len - 2));
+ *(str + (len - 2)) = '\0';
+ tm->tm_mon = atoi(str + (len - 4));
+ *(str + (len - 4)) = '\0';
+ tm->tm_year = atoi(str);
+ if ((len - 4) == 2)
+ *is2digits = TRUE;
return DTK_DATE;
}
@@ -2686,7 +2687,7 @@ DecodeNumberField(int len, char *str, int fmask,
*(str + 4) = '\0';
tm->tm_min = atoi(str + 2);
*(str + 2) = '\0';
- tm->tm_hour = atoi(str + 0);
+ tm->tm_hour = atoi(str);
return DTK_TIME;
}
@@ -2697,7 +2698,7 @@ DecodeNumberField(int len, char *str, int fmask,
tm->tm_sec = 0;
tm->tm_min = atoi(str + 2);
*(str + 2) = '\0';
- tm->tm_hour = atoi(str + 0);
+ tm->tm_hour = atoi(str);
return DTK_TIME;
}
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 6581b5e..9f4f7a4 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -1675,3 +1675,25 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
| 2001 1 1 1 1 1 1
(66 rows)
+CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
+-- Test year field value with len > 4
+INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 12 23:58:48 1000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(2, 'Sat Mar 12 23:58:48 10000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(3, 'Sat Mar 12 23:58:48 100000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(3, '10000 Mar 12 23:58:48 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(4, '100000312 23:58:48 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(4, '1000000312 23:58:48 IST');
+--Verify data
+SELECT * FROM TIMESTAMPTZ_TST ORDER BY a;
+ a | b
+---+--------------------------------
+ 1 | Wed Mar 12 13:58:48 1000 PST
+ 2 | Sun Mar 12 14:58:48 10000 PDT
+ 3 | Sun Mar 12 14:58:48 100000 PDT
+ 3 | Sun Mar 12 14:58:48 10000 PDT
+ 4 | Sun Mar 12 14:58:48 10000 PDT
+ 4 | Sun Mar 12 14:58:48 100000 PDT
+(6 rows)
+
+--Cleanup
+DROP TABLE TIMESTAMPTZ_TST;
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 863b286..4eef62e 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -240,3 +240,17 @@ SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID')
SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
FROM TIMESTAMPTZ_TBL;
+
+CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
+
+-- Test year field value with len > 4
+INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 12 23:58:48 1000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(2, 'Sat Mar 12 23:58:48 10000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(3, 'Sat Mar 12 23:58:48 100000 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(3, '10000 Mar 12 23:58:48 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(4, '100000312 23:58:48 IST');
+INSERT INTO TIMESTAMPTZ_TST VALUES(4, '1000000312 23:58:48 IST');
+--Verify data
+SELECT * FROM TIMESTAMPTZ_TST ORDER BY a;
+--Cleanup
+DROP TABLE TIMESTAMPTZ_TST;
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. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 workingselect timestamptz 'January 8, 19991 01:01:01 IST'; -- works
select timestamptz 'January 8, 199910 01:01:01 IST'; -- Not workingCREATE 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 workingplease 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. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Oct 4, 2013 at 11:35 PM, Bruce Momjian <bruce@momjian.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 workingselect timestamptz 'January 8, 19991 01:01:01 IST'; -- works
select timestamptz 'January 8, 199910 01:01:01 IST'; -- Not workingCREATE 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Oct 8, 2013 at 1:34 AM, Robert Haas <robertmhaas@gmail.com> wrote:
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 datemodule
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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers