Re: Date style handling changes between 7.4.12 and 8.2.4

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adam Witney <awitney(at)sgul(dot)ac(dot)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