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