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

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

From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
To: Daniele Varrazzo <daniele(dot)varrazzo(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-20 13:23:05
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
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?

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

In response to


pgsql-general by date

Next:From: Pavel StehuleDate: 2013-08-20 17:49:13
Subject: Re: Memory Issue with array_agg?
Previous:From: Evan D. HoffmanDate: 2013-08-20 12:27:49
Subject: effective_io_concurrency with an SSD SAN?

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