Re: timestamp and timestamptz

On 4/16/20 1:23 AM, raf wrote:
> Steve Baldwin wrote:
>
>> I'm pretty sure you are mistaken. Postgres doesn't store the 'creating'
>> time zone in a timestamptz column.
>>
>> Try doing this before re-running your test:
>>
>> set timezone to 'utc';
>>
>> What you are seeing in your test is an artifact of that timezone setting.
>>
>> Steve
>
> Thanks. You're right.
>
> create table example (t timestamptz not null);
> insert into example (t) values (timestamptz '2020-04-16 17:12:33.71768 Australia/Sydney');
> select * from example;
> set timezone to 'utc';
> select * from example;
> drop table example;
>
> Does this:
>
> CREATE TABLE
> INSERT 0 1
> t
> ------------------------------
> 2020-04-16 17:12:33.71768+10
> (1 row)
>
> SET
> t
> ------------------------------
> 2020-04-16 07:12:33.71768+00
> (1 row)
>
> DROP TABLE
>
> So it doesn't store the offset, but I've used postgres
> for 12 years without knowing that and it hasn't been
> a problem. Yay, postgres!
>
> It doesn't store the offset but, by using timestamptz,
> it knows that the timezone is UTC. That's what matters.

Well that is somewhat misleading. The value entered is stored as
timestamp with an implicit tz of 'UTC'. The issue that trips people up
is the format of the input value. If you enter an input with an offset
or correct tz value then you have given Postgres an explicit value to
work off for converting it to 'UTC'. For what is correct see here:

https://www.postgresql.org/docs/12/datatype-datetime.html#DATATYPE-TIMEZONES

If you input a value that does not have the above then Postgres uses the
SET TimeZone value to implicitly set the input value's tz(as pointed out
by David Johnson upstream). In other words Postgres does not assume an
input value is in 'UTC'. For the OP's case that could cause issues if
the timestamp in the CSV file does not have a proper offset/tz and the
client is using a tz other then 'UTC'(as pointed by others upstream).
The bottom line is that when dealing with timestamps explicit is better
then implicit.

> The fact that it knows the time zone is what makes everything
> work. Timestamp without time zone is best avoided I think.
>
> cheers,
> raf
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com