Re: [PATCHES] [HACKERS] Timezone bugs
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+' (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.c22 Jul 2005 05:03:09 - 1.118
--- src/backend/utils/adt/date.c23 Jul 2005 14:23:14 -
***
*** 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.c22 Jul 2005 03:46:33 - 1.156
--- src/backend/utils/adt/datetime.c23 Jul 2005 14:23:15 -
***
*** 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()
*
*
Re: [PATCHES] [HACKERS] Timezone bugs
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+' (convert to client timezone (UTC))
So the conversion is being done backwards, resulting in the wrong result.
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--
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: timestamp.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.141
diff -c -c -r1.141 timestamp.c
*** timestamp.c 22 Jul 2005 19:00:54 - 1.141
--- timestamp.c 23 Jul 2005 02:00:07 -
***
*** 3922,3938
}
! /* 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.
*/
Datum
timestamp_zone(PG_FUNCTION_ARGS)
{
text *zone = PG_GETARG_TEXT_P(0);
Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
! Timestamp result;
int tz;
pg_tz *tzp;
chartzname[TZ_STRLEN_MAX+1];
--- 3922,3942
}
! /*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)
{
text *zone = PG_GETARG_TEXT_P(0);
Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
! TimestampTz result;
int tz;
pg_tz *tzp;
chartzname[TZ_STRLEN_MAX+1];
***
*** 3960,3966
/* Apply the timezone change */
if (timestamp2tm(timestamp, tz, tm, fsec, NULL, tzp) != 0 ||
! tm2timestamp(tm, fsec, NULL, result) != 0)
{
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
--- 3964,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),
***
*** 3968,3974
tzname)));
PG_RETURN_NULL();
}
! PG_RETURN_TIMESTAMPTZ(timestamp2timestamptz(result));
}
/* timestamp_izone()
--- 3972,3981
tzname)));
PG_RETURN_NULL();
}
!
