Обсуждение: Date style handling changes between 7.4.12 and 8.2.4

Hi,

I am upgrading from 7.4.12 to 8.2.4 and I have run into a difference in date
style handling...

In 7.4.12 this would work

bugasbase2=# create table date_test (name date);
CREATE TABLE
bugasbase2=# insert into date_test values('Wed Jul 11 10:51:14 GMT+01:00
2001');

However in 8.2.4 this happens

bugasbase_070529=# create table date_test (name date);
CREATE TABLE
bugasbase_070529=# insert into date_test values('Wed Jul 11 10:51:14
GMT+01:00 2001');
ERROR:  invalid input syntax for type date: "Wed Jul 11 10:51:14 GMT+01:00
2001"

I assume something to do with date handling has changed?

I have found date_trunc and extract in the docs, but can't seem to get the
syntax right to get this to work... Is there a function that can convert the
string to be acceptable for 8.2.4?

Thanks in advance

adam

On Jun 12, 2007, at 4:04 , Adam Witney wrote:

> bugasbase_070529=# insert into date_test values('Wed Jul 11 10:51:14
> GMT+01:00 2001');
> ERROR:  invalid input syntax for type date: "Wed Jul 11 10:51:14 GMT
> +01:00
> 2001"

> I have found date_trunc and extract in the docs,

Both date_trunc and extract work on date and timestamp datatypes.
What you are looking for is something to convert a string to a date:
to_date should work:

http://www.postgresql.org/docs/8.2/interactive/functions-formatting.html

Michael Glaesemann
grzm seespotcode net

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

Excellent, thanks very much.

Will this make it into the general source tree? Or would I have to patch
this with future upgrades?

adam

On 12/6/07 16:51, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

> 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

Adam Witney <awitney@sgul.ac.uk> writes:
> Will this make it into the general source tree?

http://archives.postgresql.org/pgsql-committers/2007-06/msg00148.php

            regards, tom lane

What year would your example choose? 

The following works in 8.2.4:

select 'Wed Jul 11 2007 10:51:14 GMT+01:00'::timestamp with time zone
     timestamptz
------------------------
2007-07-11 06:51:14-05

It appears to ignore the day of the week, though:

select 'Mon Jul 11 2007 10:51:14 GMT+01:00'::timestamp with time zone

     timestamptz
------------------------
2007-07-11 06:51:14-05
--
Mike Nolan