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, &timestamp);
+                       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

Reply via email to