Hi, I propose the appended patch for the Todo item:
o Allow timezone names in SQL strings, '2006-05-24 21:11 Americas/New_York'::timestamptz I changed the ParseDateTime function as well as DecodeDateTime to support those timezones in timestamps and DecodeTimeOnly to support it in the timetz type. '2006-05-24 21:11 America/New_York'::timestamptz is now the same as '2006-05-24 21:11'::timestamptz at time zone 'America/New_York' With a timetz it's more tricky, because "America/New_York" does not specify a timezone offset by itself, this could change due to daylight savings time for example. So my idea was to apply whatever offset is valid in this region at the moment of parsing the string representation. It passes all existing regression tests, I will supply additional regression tests as well as documentation patches as soon as you have told me that I'm on the right track :-) Joachim
diff -cr cvs/pgsql/src/backend/utils/adt/datetime.c cvs.build/pgsql/src/backend/utils/adt/datetime.c *** cvs/pgsql/src/backend/utils/adt/datetime.c 2006-03-24 08:10:23.000000000 +0100 --- cvs.build/pgsql/src/backend/utils/adt/datetime.c 2006-06-01 07:55:32.000000000 +0200 *************** *** 23,28 **** --- 23,29 ---- #include "access/xact.h" #include "miscadmin.h" #include "utils/datetime.h" + #include "utils/builtins.h" #include "utils/guc.h" *************** *** 36,41 **** --- 37,43 ---- struct pg_tm * tm, fsec_t *fsec); static int DecodeTimezone(char *str, int *tzp); static int DecodePosixTimezone(char *str, int *tzp); + static int DecodeZicTimezone(char *str, int *tzp); static datetkn *datebsearch(char *key, datetkn *base, unsigned int nel); static int DecodeDate(char *str, int fmask, int *tmask, struct pg_tm * tm); static void TrimTrailingZeros(char *str); *************** *** 888,895 **** { char delim = *cp; ! ftype[nf] = DTK_DATE; ! APPEND_CHAR(bufp, bufend, *cp++); while (isdigit((unsigned char) *cp) || *cp == delim) APPEND_CHAR(bufp, bufend, *cp++); } --- 890,913 ---- { char delim = *cp; ! if (*cp == '/') ! { ! ftype[nf] = DTK_TZ; ! /* set the first character of the region to upper case ! * again*/ ! field[nf][0] = pg_toupper((unsigned char) field[nf][0]); ! /* we have seen "Region/" of a POSIX timezone, continue to ! * read the City part */ ! do { ! APPEND_CHAR(bufp, bufend, *cp++); ! /* there is for example America/New_York */ ! } while (isalpha((unsigned char) *cp) || *cp == '_'); ! } ! else ! { ! ftype[nf] = DTK_DATE; ! APPEND_CHAR(bufp, bufend, *cp++); ! } while (isdigit((unsigned char) *cp) || *cp == delim) APPEND_CHAR(bufp, bufend, *cp++); } *************** *** 980,985 **** --- 998,1004 ---- bool haveTextMonth = FALSE; int is2digits = FALSE; int bc = FALSE; + int zicTzFnum = -1; /* * We'll insist on at least all of the date fields, but initialize the *************** *** 1127,1133 **** if (tzp == NULL) return DTERR_BAD_FORMAT; ! dterr = DecodeTimezone(field[i], &tz); if (dterr) return dterr; --- 1146,1160 ---- if (tzp == NULL) return DTERR_BAD_FORMAT; ! if (strchr(field[i], '/') != NULL) ! { ! /* remember to apply the timezone at the end */ ! zicTzFnum = i; ! tmask = DTK_M(TZ); ! break; ! } ! else ! dterr = DecodeTimezone(field[i], &tz); if (dterr) return dterr; *************** *** 1605,1610 **** --- 1632,1650 ---- if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]) return DTERR_FIELD_OVERFLOW; + if (zicTzFnum != -1) + { + Datum tsTz; + Timestamp timestamp; + tm2timestamp(tm, *fsec, NULL, ×tamp); + tsTz = DirectFunctionCall2(timestamp_zone, + DirectFunctionCall1(textin, + CStringGetDatum(field[zicTzFnum])), + TimestampGetDatum(timestamp)); + timestamp2tm(DatumGetTimestampTz(tsTz), tzp, tm, fsec, NULL, NULL); + fmask &= ~DTK_M(TZ); + } + /* timezone not specified? then find local timezone if possible */ if (tzp != NULL && !(fmask & DTK_M(TZ))) { *************** *** 1874,1880 **** if (tzp == NULL) return DTERR_BAD_FORMAT; ! dterr = DecodeTimezone(field[i], &tz); if (dterr) return dterr; --- 1914,1923 ---- if (tzp == NULL) return DTERR_BAD_FORMAT; ! if (strchr(field[i], '/') != NULL) ! dterr = DecodeZicTimezone(field[i], &tz); ! else ! dterr = DecodeTimezone(field[i], &tz); if (dterr) return dterr; *************** *** 2924,2929 **** --- 2967,2990 ---- return 0; } + static int + DecodeZicTimezone(char *str, int *tzp) + { + TimestampTz current; + struct pg_tm tm; + struct pg_tz *tz; + fsec_t fsec = 0; + + tz = pg_tzset(str); + if (!tz) + return -1; + + current = GetCurrentTimestamp(); + timestamp2tm(current, NULL, &tm, &fsec, NULL, global_timezone); + *tzp = DetermineTimeZoneOffset(&tm, tz); + + return 0; + } /* DecodeSpecial() * Decode text string using lookup table.
---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org