Search Postgresql Archives

Re: Handling of tz-aware literals in non-tz-aware fields

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Aug 20, 2013 at 2:23 PM, Adrian Klaver <adrian.klaver@xxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux