From: | Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Handling of tz-aware literals in non-tz-aware fields |
Date: | 2013-08-22 17:57:39 |
Message-ID: | CA+mi_8b5+gEQ1DHj3GFQXjfTNuEncqEfd2UfDE8f1kK4TpDqdQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Aug 20, 2013 at 2:23 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
> 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.
Perfect. Well, probably less than perfect but perfectly documented.
A psycopg user was surprised because a Python datetime with time zone
is "erroneously treated correctly" in a query from Python (i.e. the
tzinfo is not discarded but taken in consideration)
<http://psycopg.lighthouseapp.com/projects/62710/tickets/176>. This is
because psycopg implicitly adds a timestamptz cast.
The only thing that bothers me is that in a future psycopg
implementation we may drop the cast, only relying on postgres
behaviour, and as a consequence passing a tz-aware object to a
non-tz-aware field (which is an operation that smells like
underspecified anyway) may change result. Such version would be
not-backward-compatible for other reasons, so it doesn't bother me
excessively.
-- Daniele
From | Date | Subject | |
---|---|---|---|
Next Message | Vincent Veyron | 2013-08-22 21:12:31 | Re: Locale Issue |
Previous Message | bricklen | 2013-08-22 15:44:42 | Re: Temp files on Commit |