On Thu, Jun 01, 2006 at 12:35:44PM -0400, Tom Lane wrote:
> Joachim Wieland <[EMAIL PROTECTED]> writes:
> > I'm talking about the timetz type that does not carry a date. So you don't
> > know if daylight savings time is active or not. How would you interpret the
> > full timezone in this case without a date?

> Oh, doh, I managed to miss that detail.  Yeah, you're right, you need an
> arbitrary assumption in that case.  Or we could forbid these timezones
> in timetz input, but that's probably not very helpful.

After sending my last mail, I concluded that it was in fact me who missed
something and that you were right. I came to the conclusion that you were
talking about the fact that you can specify a timetz also with a date:

template1=# select '2006-06-01 10:49 America/New_York'::timetz;
   timetz
-------------
 10:49:00-04

This date can then be used to infer the timezone:

template1=# select '2006-03-01 10:49 America/New_York'::timetz;
   timetz
-------------
 10:49:00-05

I have updated my patch to do so. Just specifying a timestamp

        select '10:49 America/New_York'::timetz;

does now return an error.

Is that a suitable compromise?



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 
19:19:04.000000000 +0200
***************
*** 22,27 ****
--- 22,28 ----
  
  #include "access/xact.h"
  #include "miscadmin.h"
+ #include "utils/builtins.h"
  #include "utils/datetime.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, struct pg_tm * tm);
  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,1928 ----
                                        if (tzp == NULL)
                                                return DTERR_BAD_FORMAT;
  
!                                       if (strchr(field[i], '/') != NULL)
!                                       {
!                                               /* a date has to be specified */
!                                               if ((fmask & DTK_DATE_M) != 
DTK_DATE_M)
!                                                       return DTERR_BAD_FORMAT;
!                                               dterr = 
DecodeZicTimezone(field[i], &tz, tm);
!                                       }
!                                       else
!                                               dterr = 
DecodeTimezone(field[i], &tz);
                                        if (dterr)
                                                return dterr;
  
***************
*** 2924,2929 ****
--- 2972,2990 ----
        return 0;
  }
  
+ static int
+ DecodeZicTimezone(char *str, int *tzp, struct pg_tm * tm)
+ {
+       struct pg_tz *tz;
+ 
+       tz = pg_tzset(str);
+       if (!tz)
+               return DTERR_BAD_FORMAT;
+ 
+       *tzp = DetermineTimeZoneOffset(tm, tz);
+ 
+       return 0;
+ }
  
  /* DecodeSpecial()
   * Decode text string using lookup table.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to