Thread: Handling of tz-aware literals in non-tz-aware fields
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?
Thank you very much,
-- Daniele
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? http://www.postgresql.org/docs/9.2/interactive/datatype-datetime.html#DATATYPE-DATETIME-INPUT 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 adrian.klaver@gmail.com
Hi, I am planning Streaming Replication to a new remote server, can you please suggest how to set up data encryption by SSL ortunnelling in Postgresql? regards
Em 21/08/2013 01:07, ascot.moss@gmail.com escreveu: > Hi, > > I am planning Streaming Replication to a new remote server, can you please suggest how to set up data encryption by SSLor tunnelling in Postgresql? > > regards > I've implemented streaming replication using OpenVPN as encrypted tunneling solution with high success. Just follow OpenVPN tutorial to establish your virtual private network, enable the virtual IP address in PostgreSQL configuration and establish the replication. Regards, Edson
On 8/20/2013 9:07 PM, ascot.moss@gmail.com wrote: > I am planning Streaming Replication to a new remote server, can you please suggest how to set up data encryption by SSLor tunnelling in Postgresql? see http://www.postgresql.org/docs/current/static/ssl-tcp.html -- john r pierce 37N 122W somewhere on the middle of the left coast
On Tue, Aug 20, 2013 at 2:23 PM, Adrian Klaver <adrian.klaver@gmail.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