On 08/20/2013 04:00 AM, Daniele Varrazzo wrote:
Hello,
the issue can be show with this example:
piro=> SET TIMEZONE = 'America/New_York';
SET
piro=> select '1970-01-01T00:00:00+03:00'::timestamp;
timestamp
---------------------
1970-01-01 00:00:00
(1 row)
piro=> select '1970-01-01T00:00:00+03:00'::timestamptz::timestamp;
timestamp
---------------------
1969-12-31 16:00:00
(1 row)
I find surprising that an unknown literal containing a TZ-aware
timestamp has the tz info discarded (e.g. upon insertion in a
timestamp without time zone field), whereas the cast from tz-aware to
non-tz-aware performs a conversion. I find the second behaviour much
more reasonable.
Is there an explanation for the first behaviour?
Is the first behaviour documented?
http://www.postgresql.org/docs/9.2/interactive/datatype-datetime.html#DATATYPE-DATETIME-INPUT
The SQL standard differentiates timestamp without time zone and
timestamp with time zone literals by the presence of a "+" or "-" symbol
and time zone offset after the time. Hence, according to the standard,
TIMESTAMP '2004-10-19 10:23:54'
is a timestamp without time zone, while
TIMESTAMP '2004-10-19 10:23:54+02'
is a timestamp with time zone. PostgreSQL never examines the content of
a literal string before determining its type, and therefore will treat
both of the above as timestamp without time zone. To ensure that a
literal is treated as timestamp with time zone, give it the correct
explicit type:
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
In a literal that has been determined to be timestamp without time zone,
PostgreSQL will silently ignore any time zone indication. That is, the
resulting value is derived from the date/time fields in the input value,
and is not adjusted for time zone.
Thank you very much,
-- Daniele
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
