Re: Date style handling changes between 7.4.12 and 8.2.4 - Mailing list pgsql-general

Adam Witney <awitney@sgul.ac.uk> writes:
> In 7.4.12 this would work
> bugasbase2=# insert into date_test values('Wed Jul 11 10:51:14 GMT+01:00 2001');

Hmm, there's an intentional and an unintentional change here.  The
unintentional one is that that field order (tz before year) doesn't work
anymore.  Truth is that it only worked for rather small values of "work"
even in 7.4:

regression=# select 'Wed Jul 11 10:51:14 GMT+01:00 2001'::timestamptz;
      timestamptz
------------------------
 2001-07-11 07:51:14-04
(1 row)

regression=# select 'Wed Jul 11 10:51:14 GMT-01:00 2001'::timestamptz;
ERROR:  invalid input syntax for type timestamp with time zone: "Wed Jul 11 10:51:14 GMT-01:00 2001"

but as of 8.2 it fails for both the + and - variants.  I think the
attached patch will fix it for you.

The intentional change is that a timezone in that POSIXy format
(ABBREV+-OFFSET) is now interpreted as meaning exactly the offset;
the ABBREV part is noise.  This is per POSIX spec as far as I can tell,
but it's not what the code used to do.  Won't affect you since "GMT"
is offset 0 anyway, but it's worth pointing out.

            regards, tom lane


Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.174.2.1
diff -c -r1.174.2.1 datetime.c
*** src/backend/utils/adt/datetime.c    29 May 2007 04:59:13 -0000    1.174.2.1
--- src/backend/utils/adt/datetime.c    12 Jun 2007 15:47:21 -0000
***************
*** 719,729 ****
                  }
                  /***
                   * Already have a date? Then this might be a time zone name
!                  * with embedded punctuation (e.g. "America/New_York") or
!                  * a run-together time with trailing time zone (e.g. hhmmss-zz).
                   * - thomas 2001-12-25
                   ***/
!                 else if ((fmask & DTK_DATE_M) == DTK_DATE_M || ptype != 0)
                  {
                      /* No time zone accepted? Then quit... */
                      if (tzp == NULL)
--- 719,735 ----
                  }
                  /***
                   * Already have a date? Then this might be a time zone name
!                  * with embedded punctuation (e.g. "America/New_York") or a
!                  * run-together time with trailing time zone (e.g. hhmmss-zz).
                   * - thomas 2001-12-25
+                  *
+                  * We consider it a time zone if we already have month & day.
+                  * This is to allow the form "mmm dd hhmmss tz year", which
+                  * we've historically accepted.
                   ***/
!                 else if (ptype != 0 ||
!                          ((fmask & (DTK_M(MONTH) | DTK_M(DAY))) ==
!                           (DTK_M(MONTH) | DTK_M(DAY))))
                  {
                      /* No time zone accepted? Then quit... */
                      if (tzp == NULL)
Index: src/test/regress/expected/timestamptz.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/timestamptz.out,v
retrieving revision 1.21.2.1
diff -c -r1.21.2.1 timestamptz.out
*** src/test/regress/expected/timestamptz.out    12 Jan 2007 23:35:04 -0000    1.21.2.1
--- src/test/regress/expected/timestamptz.out    12 Jun 2007 15:47:21 -0000
***************
*** 153,158 ****
--- 153,190 ----
  ERROR:  time zone displacement out of range: "Feb 16 17:32:01 -0097"
  INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 5097 BC');
  ERROR:  timestamp out of range: "Feb 16 17:32:01 5097 BC"
+ -- Alternate field order that we've historically supported (sort of)
+ -- with regular and POSIXy timezone specs
+ SELECT 'Wed Jul 11 10:51:14 America/New_York 2001'::timestamptz;
+          timestamptz
+ ------------------------------
+  Wed Jul 11 07:51:14 2001 PDT
+ (1 row)
+
+ SELECT 'Wed Jul 11 10:51:14 GMT-4 2001'::timestamptz;
+          timestamptz
+ ------------------------------
+  Tue Jul 10 23:51:14 2001 PDT
+ (1 row)
+
+ SELECT 'Wed Jul 11 10:51:14 GMT+4 2001'::timestamptz;
+          timestamptz
+ ------------------------------
+  Wed Jul 11 07:51:14 2001 PDT
+ (1 row)
+
+ SELECT 'Wed Jul 11 10:51:14 PST-03:00 2001'::timestamptz;
+          timestamptz
+ ------------------------------
+  Wed Jul 11 00:51:14 2001 PDT
+ (1 row)
+
+ SELECT 'Wed Jul 11 10:51:14 PST+03:00 2001'::timestamptz;
+          timestamptz
+ ------------------------------
+  Wed Jul 11 06:51:14 2001 PDT
+ (1 row)
+
  SELECT '' AS "64", d1 FROM TIMESTAMPTZ_TBL;
   64 |               d1
  ----+---------------------------------
Index: src/test/regress/sql/timestamptz.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/timestamptz.sql,v
retrieving revision 1.10
diff -c -r1.10 timestamptz.sql
*** src/test/regress/sql/timestamptz.sql    17 Oct 2006 21:03:21 -0000    1.10
--- src/test/regress/sql/timestamptz.sql    12 Jun 2007 15:47:21 -0000
***************
*** 127,132 ****
--- 127,140 ----
  INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 -0097');
  INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 5097 BC');

+ -- Alternate field order that we've historically supported (sort of)
+ -- with regular and POSIXy timezone specs
+ SELECT 'Wed Jul 11 10:51:14 America/New_York 2001'::timestamptz;
+ SELECT 'Wed Jul 11 10:51:14 GMT-4 2001'::timestamptz;
+ SELECT 'Wed Jul 11 10:51:14 GMT+4 2001'::timestamptz;
+ SELECT 'Wed Jul 11 10:51:14 PST-03:00 2001'::timestamptz;
+ SELECT 'Wed Jul 11 10:51:14 PST+03:00 2001'::timestamptz;
+
  SELECT '' AS "64", d1 FROM TIMESTAMPTZ_TBL;

  -- Demonstrate functions and operators