Andrew pointed out that the current fix didn't handle dates that were
near daylight savings time boudaries. This handles it properly, e.g.
test=> select '2005-04-03 04:00:00'::timestamp at time zone
'America/Los_Angeles';
timezone
------------------------
2005-04-03 07:00:00-04
(1 row)
Patch attached and applied. The new fix is cleaner too.
---------------------------------------------------------------------------
pgman wrote:
>
> OK, tricky, but fixed --- patch attached and applied, with documentation
> updates. Here is the test query:
>
> test=> select (CURRENT_DATE + '05:00'::time)::timestamp at time zone
> 'Canada/Pacific';
> timezone
> ------------------------
> 2005-07-22 08:00:00-04
> (1 row)
>
> I tested a bunch of others too, like:
>
> test=> select ('2005-07-20 00:00:00'::timestamp without time zone) at
> time zone 'Europe/Paris';
> timezone
> ------------------------
> 2005-07-19 18:00:00-04
> (1 row)
>
> and tested that for UTC also.
>
> It was hard to figure out how to cleanly adjust the time zone. I added
> some comments explaining the process.
>
> ---------------------------------------------------------------------------
>
> Andrew - Supernews wrote:
> > On 2005-07-22, Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> > >>
> > >> select (CURRENT_DATE + '05:00'::time)::timestamp
> > >> at time zone 'Canada/Pacific';
> > >> timezone
> > >> ------------------------
> > >> 2005-07-19 22:00:00+00
> > >> (1 row)
> > >>
> > > What is happening here is that 2005-07-20 05:00:00 is being cast back 7
> > > hours (Canada/Pacific offset), and that is 22:00 of the previous day.
> >
> > Which is of course completely wrong.
> >
> > Let's look at what should happen:
> >
> > (date + time) = timestamp without time zone
> >
> > '2005-07-20' + '05:00' = '2005-07-20 05:00:00'::timestamp
> >
> > (timestamp without time zone) AT TIME ZONE 'zone'
> >
> > When AT TIME ZONE is applied to a timestamp without time zone, it is
> > supposed to keep the _same_ calendar time and return a result of type
> > timestamp with time zone designating the absolute time. So in this case,
> > we expect the following to happen:
> >
> > '2005-07-20 05:00:00' (original timestamp)
> > -> '2005-07-20 05:00:00-0700' (same calendar time in new zone)
> > -> '2005-07-20 12:00:00+0000' (convert to client timezone (UTC))
> >
> > So the conversion is being done backwards, resulting in the wrong result.
> >
> > --
> > Andrew, Supernews
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Index: src/backend/utils/adt/date.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/date.c,v
retrieving revision 1.118
diff -c -c -r1.118 date.c
*** src/backend/utils/adt/date.c 22 Jul 2005 05:03:09 -0000 1.118
--- src/backend/utils/adt/date.c 23 Jul 2005 14:23:14 -0000
***************
*** 301,307 ****
tm->tm_hour = 0;
tm->tm_min = 0;
tm->tm_sec = 0;
! tz = DetermineLocalTimeZone(tm);
#ifdef HAVE_INT64_TIMESTAMP
result = dateVal * USECS_PER_DAY + tz * USECS_PER_SEC;
--- 301,307 ----
tm->tm_hour = 0;
tm->tm_min = 0;
tm->tm_sec = 0;
! tz = DetermineTimeZoneOffset(tm, global_timezone);
#ifdef HAVE_INT64_TIMESTAMP
result = dateVal * USECS_PER_DAY + tz * USECS_PER_SEC;
***************
*** 2231,2237 ****
GetCurrentDateTime(tm);
time2tm(time, tm, &fsec);
! tz = DetermineLocalTimeZone(tm);
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
--- 2231,2237 ----
GetCurrentDateTime(tm);
time2tm(time, tm, &fsec);
! tz = DetermineTimeZoneOffset(tm, global_timezone);
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.156
diff -c -c -r1.156 datetime.c
*** src/backend/utils/adt/datetime.c 22 Jul 2005 03:46:33 -0000 1.156
--- src/backend/utils/adt/datetime.c 23 Jul 2005 14:23:15 -0000
***************
*** 1612,1618 ****
if (fmask & DTK_M(DTZMOD))
return DTERR_BAD_FORMAT;
! *tzp = DetermineLocalTimeZone(tm);
}
}
--- 1612,1618 ----
if (fmask & DTK_M(DTZMOD))
return DTERR_BAD_FORMAT;
! *tzp = DetermineTimeZoneOffset(tm, global_timezone);
}
}
***************
*** 1620,1629 ****
}
! /* DetermineLocalTimeZone()
*
* Given a struct pg_tm in which tm_year, tm_mon, tm_mday, tm_hour, tm_min,
and
! * tm_sec fields are set, attempt to determine the applicable local zone
* (ie, regular or daylight-savings time) at that time. Set the struct
pg_tm's
* tm_isdst field accordingly, and return the actual timezone offset.
*
--- 1620,1629 ----
}
! /* DetermineTimeZoneOffset()
*
* Given a struct pg_tm in which tm_year, tm_mon, tm_mday, tm_hour, tm_min,
and
! * tm_sec fields are set, attempt to determine the applicable time zone
* (ie, regular or daylight-savings time) at that time. Set the struct
pg_tm's
* tm_isdst field accordingly, and return the actual timezone offset.
*
***************
*** 1632,1638 ****
* of mktime(), anyway.
*/
int
! DetermineLocalTimeZone(struct pg_tm *tm)
{
int date,
sec;
--- 1632,1638 ----
* of mktime(), anyway.
*/
int
! DetermineTimeZoneOffset(struct pg_tm *tm, pg_tz *tzp)
{
int date,
sec;
***************
*** 1648,1654 ****
after_isdst;
int res;
! if (HasCTZSet)
{
tm->tm_isdst = 0; /* for lack of a better idea */
return CTimeZone;
--- 1648,1654 ----
after_isdst;
int res;
! if (tzp == global_timezone && HasCTZSet)
{
tm->tm_isdst = 0; /* for lack of a better idea */
return CTimeZone;
***************
*** 1687,1693 ****
&before_gmtoff,
&before_isdst,
&boundary,
&after_gmtoff,
&after_isdst,
! global_timezone);
if (res < 0)
goto overflow; /* failure? */
--- 1687,1693 ----
&before_gmtoff,
&before_isdst,
&boundary,
&after_gmtoff,
&after_isdst,
! tzp);
if (res < 0)
goto overflow; /* failure? */
***************
*** 2282,2288 ****
tmp->tm_hour = tm->tm_hour;
tmp->tm_min = tm->tm_min;
tmp->tm_sec = tm->tm_sec;
! *tzp = DetermineLocalTimeZone(tmp);
tm->tm_isdst = tmp->tm_isdst;
}
--- 2282,2288 ----
tmp->tm_hour = tm->tm_hour;
tmp->tm_min = tm->tm_min;
tmp->tm_sec = tm->tm_sec;
! *tzp = DetermineTimeZoneOffset(tmp, global_timezone);
tm->tm_isdst = tmp->tm_isdst;
}
Index: src/backend/utils/adt/formatting.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v
retrieving revision 1.92
diff -c -c -r1.92 formatting.c
*** src/backend/utils/adt/formatting.c 21 Jul 2005 03:56:16 -0000 1.92
--- src/backend/utils/adt/formatting.c 23 Jul 2005 14:23:17 -0000
***************
*** 2989,2995 ****
do_to_timestamp(date_txt, fmt, &tm, &fsec);
! tz = DetermineLocalTimeZone(&tm);
if (tm2timestamp(&tm, fsec, &tz, &result) != 0)
ereport(ERROR,
--- 2989,2995 ----
do_to_timestamp(date_txt, fmt, &tm, &fsec);
! tz = DetermineTimeZoneOffset(&tm, global_timezone);
if (tm2timestamp(&tm, fsec, &tz, &result) != 0)
ereport(ERROR,
Index: src/backend/utils/adt/nabstime.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/nabstime.c,v
retrieving revision 1.141
diff -c -c -r1.141 nabstime.c
*** src/backend/utils/adt/nabstime.c 22 Jul 2005 19:55:50 -0000 1.141
--- src/backend/utils/adt/nabstime.c 23 Jul 2005 14:23:18 -0000
***************
*** 474,480 ****
result = NOEND_ABSTIME;
else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
{
! tz = DetermineLocalTimeZone(tm);
result = tm2abstime(tm, tz);
}
else
--- 474,480 ----
result = NOEND_ABSTIME;
else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
{
! tz = DetermineTimeZoneOffset(tm, global_timezone);
result = tm2abstime(tm, tz);
}
else
Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.143
diff -c -c -r1.143 timestamp.c
*** src/backend/utils/adt/timestamp.c 23 Jul 2005 02:02:27 -0000 1.143
--- src/backend/utils/adt/timestamp.c 23 Jul 2005 14:23:19 -0000
***************
*** 2100,2106 ****
if (tm->tm_mday >
day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
tm->tm_mday =
(day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);
! tz = DetermineLocalTimeZone(tm);
if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
ereport(ERROR,
--- 2100,2106 ----
if (tm->tm_mday >
day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
tm->tm_mday =
(day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);
! tz = DetermineTimeZoneOffset(tm, global_timezone);
if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
ereport(ERROR,
***************
*** 2124,2130 ****
julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) +
span->day;
j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
! tz = DetermineLocalTimeZone(tm);
if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
ereport(ERROR,
--- 2124,2130 ----
julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) +
span->day;
j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
! tz = DetermineTimeZoneOffset(tm, global_timezone);
if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
ereport(ERROR,
***************
*** 3104,3110 ****
}
if (redotz)
! tz = DetermineLocalTimeZone(tm);
if (tm2timestamp(tm, fsec, &tz, &result) != 0)
ereport(ERROR,
--- 3104,3110 ----
}
if (redotz)
! tz = DetermineTimeZoneOffset(tm, global_timezone);
if (tm2timestamp(tm, fsec, &tz, &result) != 0)
ereport(ERROR,
***************
*** 3529,3535 ****
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out
of range")));
! tz = DetermineLocalTimeZone(tm);
if (tm2timestamp(tm, fsec, &tz,
×tamptz) != 0)
ereport(ERROR,
--- 3529,3535 ----
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out
of range")));
! tz = DetermineTimeZoneOffset(tm,
global_timezone);
if (tm2timestamp(tm, fsec, &tz,
×tamptz) != 0)
ereport(ERROR,
***************
*** 3924,3935 ****
/* timestamp_zone()
* Encode timestamp type with specified time zone.
! * Returns timestamp with time zone, with the input
! * rotated from local time to the specified zone.
! * This function is tricky because instead of shifting
! * the time _to_ a new time zone, it sets the time to _be_
! * the specified timezone. This requires trickery
! * of double-subtracting the requested timezone offset.
*/
Datum
timestamp_zone(PG_FUNCTION_ARGS)
--- 3924,3934 ----
/* timestamp_zone()
* Encode timestamp type with specified time zone.
! * This function is just timestamp2timestamptz() except instead of
! * shifting to the global timezone, we shift to the specified timezone.
! * This is different from the other AT TIME ZONE cases because instead
! * of shifting to a _to_ a new time zone, it sets the time to _be_ the
! * specified timezone.
*/
Datum
timestamp_zone(PG_FUNCTION_ARGS)
***************
*** 3943,3953 ****
int len;
struct pg_tm tm;
fsec_t fsec;
!
if (TIMESTAMP_NOT_FINITE(timestamp))
PG_RETURN_TIMESTAMPTZ(timestamp);
! /* Find the specified timezone? */
len = (VARSIZE(zone) - VARHDRSZ>TZ_STRLEN_MAX) ?
TZ_STRLEN_MAX : VARSIZE(zone) - VARHDRSZ;
memcpy(tzname, VARDATA(zone), len);
--- 3942,3953 ----
int len;
struct pg_tm tm;
fsec_t fsec;
! bool fail;
!
if (TIMESTAMP_NOT_FINITE(timestamp))
PG_RETURN_TIMESTAMPTZ(timestamp);
! /* Find the specified timezone */
len = (VARSIZE(zone) - VARHDRSZ>TZ_STRLEN_MAX) ?
TZ_STRLEN_MAX : VARSIZE(zone) - VARHDRSZ;
memcpy(tzname, VARDATA(zone), len);
***************
*** 3963,3970 ****
}
/* Apply the timezone change */
! if (timestamp2tm(timestamp, &tz, &tm, &fsec, NULL, tzp) != 0 ||
! tm2timestamp(&tm, fsec, &tz, &result) != 0)
{
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
--- 3963,3975 ----
}
/* Apply the timezone change */
! fail = (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, tzp) != 0);
! if (!fail)
! {
! tz = DetermineTimeZoneOffset(&tm, tzp);
! fail = (tm2timestamp(&tm, fsec, &tz, &result) != 0);
! }
! if (fail)
{
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
***************
*** 3972,3979 ****
tzname)));
PG_RETURN_NULL();
}
- /* Must double-adjust for timezone */
- result = dt2local(result, -tz);
PG_RETURN_TIMESTAMPTZ(result);
}
--- 3977,3982 ----
***************
*** 4039,4045 ****
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
! tz = DetermineLocalTimeZone(tm);
if (tm2timestamp(tm, fsec, &tz, &result) != 0)
ereport(ERROR,
--- 4042,4048 ----
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
! tz = DetermineTimeZoneOffset(tm, global_timezone);
if (tm2timestamp(tm, fsec, &tz, &result) != 0)
ereport(ERROR,
Index: src/include/utils/datetime.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/datetime.h,v
retrieving revision 1.55
diff -c -c -r1.55 datetime.h
*** src/include/utils/datetime.h 22 Jul 2005 03:46:34 -0000 1.55
--- src/include/utils/datetime.h 23 Jul 2005 14:23:20 -0000
***************
*** 291,297 ****
extern void DateTimeParseError(int dterr, const char *str,
const char *datatype);
! extern int DetermineLocalTimeZone(struct pg_tm *tm);
extern int EncodeDateOnly(struct pg_tm *tm, int style, char *str);
extern int EncodeTimeOnly(struct pg_tm *tm, fsec_t fsec, int *tzp, int
style, char *str);
--- 291,297 ----
extern void DateTimeParseError(int dterr, const char *str,
const char *datatype);
! extern int DetermineTimeZoneOffset(struct pg_tm *tm, pg_tz *tzp);
extern int EncodeDateOnly(struct pg_tm *tm, int style, char *str);
extern int EncodeTimeOnly(struct pg_tm *tm, fsec_t fsec, int *tzp, int
style, char *str);
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
