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

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

From: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Handling of tz-aware literals in non-tz-aware fields
Date: 2013-08-20 11:00:07
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general

the issue can be show with this example:

    piro=> SET TIMEZONE = 'America/New_York';

    piro=> select '1970-01-01T00:00:00+03:00'::timestamp;
     1970-01-01 00:00:00
    (1 row)

    piro=> select '1970-01-01T00:00:00+03:00'::timestamptz::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


pgsql-general by date

Next:From: Evan D. HoffmanDate: 2013-08-20 12:27:49
Subject: effective_io_concurrency with an SSD SAN?
Previous:From: Magnus HaganderDate: 2013-08-20 09:10:53
Subject: Re: Strange message from pg_receivexlog

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