Re: timestamp and timestamptz - Mailing list pgsql-general

Adrian Klaver wrote:

> 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'.

When I said "it knows that the timezone is UTC", I was only referring
to the values once stored as a timestamptz, not the input. Sorry that
wasn't clear enough. I meant to say that once a value is stored in a
timestamptz (as opposed to a timestamp), postgres knows that it is
stored in UTC and will do the right things with it.

> 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.

I couldn't agree more.

> > 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.klaver@aklaver.com
>