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, ×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,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
