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 <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
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;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
