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
