Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
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


In response to

pgsql-general by date

Next:From: Vincent VeyronDate: 2013-08-22 21:12:31
Subject: Re: Locale Issue
Previous:From: bricklenDate: 2013-08-22 15:44:42
Subject: Re: Temp files on Commit

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group