Timezone bugs
There appear to be several bugs in the at time zone patch recently applied.
show timezone;
TimeZone
----------
UTC
(1 row)
select now();
now
-------------------------------
2005-07-20 23:38:57.981128+00
(1 row)
*** WORKS ***
select CURRENT_DATE + '05:00'::time at time zone 'Canada/Pacific';
?column?
------------------------
2005-07-21 05:00:00+00
(1 row)
*** BROKEN ***
select (CURRENT_DATE + '05:00'::time)::timestamp at time zone 'Canada/Pacific';
timezone
------------------------
2005-07-19 22:00:00+00
(1 row)
Wrong date _and_ time. (Ive not even got a theory about how the date gets wrong here)
select '05:00'::time at time zone 'Canada/Pacific';
timezone
-------------
22:00:00-07
(1 row)
Wrong time.
I think there's something fishy going on with internal casts.
if time has no timezone it should not imply a cast to timetz using the local timezone, instead time to at time zone should ADD timezone information to the datatype to result in a timetz though that may require the use of something like select '05:00'::time at time zone 'Canada/Pacific' on CURRENT_DATE::date to do properly.
Suggested resolution would be to allow the actual storage of named timezone descriptions like 'Canada/Pacfiic' within the timetz datatype natively instead of converting to utc for storage (which is logically invalid).
The application of this whole problem is for a VoIP network to be able to handle evening and weekend calling based on cities. Evening being after 6pm in vacouver per se. Current and historical processing of the time zone data is also a requirement.
Hope that helps.
Kevin McArthur
Director
StormTide Digital Studios Inc.
My guess is that is this commit that is causing the problem:
revision 1.110
date: 2005/06/15 00:34:08; author: momjian; state: Exp; lines: +48 -40
This patch makes it possible to use the full set of timezones when doing
"AT TIME ZONE", and not just the shorlist previously available. For
example:
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Europe/London';
works fine now. It will also obey whatever DST rules were in effect at
just that date, which the previous implementation did not.
It also supports the AT TIME ZONE on the timetz datatype. The whole
handling of DST is a bit bogus there, so I chose to make it use whatever
DST rules are in effect at the time of executig the query. not sure if
anybody is actuallyi *using* timetz though, it seems pretty
unpredictable just because of this...
Magnus Hagander
I will research it tomorrow and report back.
---------------------------------------------------------------------------
Kevin McArthur wrote:
There appear to be several bugs in the at time zone patch recently applied.
show timezone;
TimeZone
----------
UTC
(1 row)select now();
now
-------------------------------
2005-07-20 23:38:57.981128+00
(1 row)*** WORKS ***
select CURRENT_DATE + '05:00'::time at time zone 'Canada/Pacific';
?column?
------------------------
2005-07-21 05:00:00+00
(1 row)*** BROKEN ***
select (CURRENT_DATE + '05:00'::time)::timestamp at time zone 'Canada/Pacific';
timezone
------------------------
2005-07-19 22:00:00+00
(1 row)Wrong date _and_ time. (Ive not even got a theory about how the date gets wrong here)
select '05:00'::time at time zone 'Canada/Pacific';
timezone
-------------
22:00:00-07
(1 row)Wrong time.
I think there's something fishy going on with internal casts.
if time has no timezone it should not imply a cast to timetz using the local timezone, instead time to at time zone should ADD timezone information to the datatype to result in a timetz though that may require the use of something like select '05:00'::time at time zone 'Canada/Pacific' on CURRENT_DATE::date to do properly.
Suggested resolution would be to allow the actual storage of named timezone descriptions like 'Canada/Pacfiic' within the timetz datatype natively instead of converting to utc for storage (which is logically invalid).
The application of this whole problem is for a VoIP network to be able to handle evening and weekend calling based on cities. Evening being after 6pm in vacouver per se. Current and historical processing of the time zone data is also a requirement.
Hope that helps.
Kevin McArthur
Director
StormTide Digital Studios Inc.
--
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 19073Kevin McArthur wrote:
There appear to be several bugs in the at time zone patch recently applied.
show timezone;
TimeZone
----------
UTC
(1 row)select now();
now
-------------------------------
2005-07-20 23:38:57.981128+00
(1 row)*** WORKS ***
select CURRENT_DATE + '05:00'::time at time zone 'Canada/Pacific';
?column?
------------------------
2005-07-21 05:00:00+00
(1 row)
OK.
*** BROKEN ***
select (CURRENT_DATE + '05:00'::time)::timestamp at time zone 'Canada/Pacific';
timezone
------------------------
2005-07-19 22:00:00+00
(1 row)Wrong date _and_ time. (Ive not even got a theory about how the date gets wrong here)
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.
select '05:00'::time at time zone 'Canada/Pacific';
timezone
-------------
22:00:00-07
(1 row)Wrong time.
Well, again 5am GMT is 22:00 Canada/Pacific, no?
---------------------------------------------------------------------------
I think there's something fishy going on with internal casts.
if time has no timezone it should not imply a cast to timetz using the
local timezone, instead time to at time zone should ADD timezone
information to the datatype to result in a timetz though that may
require the use of something like select '05:00'::time at time zone
'Canada/Pacific' on CURRENT_DATE::date to do properly.Suggested resolution would be to allow the actual storage of named
timezone descriptions like 'Canada/Pacfiic' within the timetz datatype
natively instead of converting to utc for storage (which is logically
invalid).The application of this whole problem is for a VoIP network to be able
to handle evening and weekend calling based on cities. Evening being
after 6pm in vacouver per se. Current and historical processing of the
time zone data is also a requirement.Hope that helps.
Kevin McArthur
Director
StormTide Digital Studios Inc.
--
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
On Jul 21, 2005, at 11:57 PM, Bruce Momjian wrote:
works fine now. It will also obey whatever DST rules were in
effect at
just that date, which the previous implementation did not.
Speaking of that, would the nearly passed US bill to extend daylight
savings screw up our timezone & dst things?
http://usgovinfo.about.com/od/consumerawareness/a/dstextend.htm
(nutshell summary: DST would be changed from april-oct to mar-nov)
afaik it has passed the house & senate it just needs the prez to sign
it.
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
Jeff Trout wrote:
On Jul 21, 2005, at 11:57 PM, Bruce Momjian wrote:
works fine now. It will also obey whatever DST rules were in
effect at
just that date, which the previous implementation did not.
Speaking of that, would the nearly passed US bill to extend daylight
savings screw up our timezone & dst things?http://usgovinfo.about.com/od/consumerawareness/a/dstextend.htm
(nutshell summary: DST would be changed from april-oct to mar-nov)
afaik it has passed the house & senate it just needs the prez to sign
it.
It means we would have to update our timezone database, and perhaps
backpatch the fix to 8.0.X.
--
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
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
http://www.supernews.com - individual and corporate NNTP services
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
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 19073Attachments:
/bjm/difftext/plainDownload
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 -0000 1.141
--- timestamp.c 23 Jul 2005 02:00:07 -0000
***************
*** 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;
char tzname[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;
char tzname[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();
}
! /* Must double-adjust for timezone */
! result = dt2local(result, -tz);
!
! PG_RETURN_TIMESTAMPTZ(result);
}
/* timestamp_izone()
Index: func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.268
retrieving revision 1.269
diff -c -c -r1.268 -r1.269
*** func.sgml 20 Jul 2005 16:42:29 -0000 1.268
--- func.sgml 22 Jul 2005 21:16:14 -0000 1.269
***************
*** 5693,5699 ****
<literal><type>timestamp without time zone</type> AT TIME ZONE <replaceable>zone</></literal>
</entry>
<entry><type>timestamp with time zone</type></entry>
! <entry>Convert local time in given time zone to UTC</entry>
</row>
<row>
--- 5693,5699 ----
<literal><type>timestamp without time zone</type> AT TIME ZONE <replaceable>zone</></literal>
</entry>
<entry><type>timestamp with time zone</type></entry>
! <entry>Treat given timestamp <emphasis>without time zone</> as located in the specified time zone</entry>
</row>
<row>
***************
*** 5701,5707 ****
<literal><type>timestamp with time zone</type> AT TIME ZONE <replaceable>zone</></literal>
</entry>
<entry><type>timestamp without time zone</type></entry>
! <entry>Convert UTC to local time in given time zone</entry>
</row>
<row>
--- 5701,5707 ----
<literal><type>timestamp with time zone</type> AT TIME ZONE <replaceable>zone</></literal>
</entry>
<entry><type>timestamp without time zone</type></entry>
! <entry>Convert given timestamp <emphasis>with time zone</> to the new time zone</entry>
</row>
<row>
***************
*** 5709,5715 ****
<literal><type>time with time zone</type> AT TIME ZONE <replaceable>zone</></literal>
</entry>
<entry><type>time with time zone</type></entry>
! <entry>Convert local time across time zones</entry>
</row>
</tbody>
</tgroup>
--- 5709,5715 ----
<literal><type>time with time zone</type> AT TIME ZONE <replaceable>zone</></literal>
</entry>
<entry><type>time with time zone</type></entry>
! <entry>Convert given time <emphasis>with time zone</> to the new time zone</entry>
</row>
</tbody>
</tgroup>
***************
*** 5720,5726 ****
specified either as a text string (e.g., <literal>'PST'</literal>)
or as an interval (e.g., <literal>INTERVAL '-08:00'</literal>).
In the text case, the available zone names are those shown in
! <xref linkend="datetime-timezone-set-table">.
</para>
<para>
--- 5720,5727 ----
specified either as a text string (e.g., <literal>'PST'</literal>)
or as an interval (e.g., <literal>INTERVAL '-08:00'</literal>).
In the text case, the available zone names are those shown in
! <xref linkend="datetime-timezone-set-table">. The time zone can
! also be implied using the default time zone for that session.
</para>
<para>
***************
*** 5732,5741 ****
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
</screen>
! The first example takes a zone-less time stamp and interprets it as MST time
! (UTC-7) to produce a UTC time stamp, which is then rotated to PST (UTC-8)
! for display. The second example takes a time stamp specified in EST
! (UTC-5) and converts it to local time in MST (UTC-7).
</para>
<para>
--- 5733,5741 ----
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
</screen>
! The first example takes a time stamp without time zone and interprets it as MST time
! (UTC-7), which is then converted to PST (UTC-8) for display. The second example takes
! a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7).
</para>
<para>