Re: timestamp and timestamptz

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.
The fact that it knows the time zone is what makes everything
work. Timestamp without time zone is best avoided I think.

cheers,
raf