Re: timestamp and timestamptz - Mailing list pgsql-general

David G. Johnston wrote:

> On Wed, Apr 15, 2020 at 4:53 PM raf <raf@raf.org> wrote:
> 
> > I don't see much difference in storing a timestamptz in UTC or a
> > timestamptz
> > in CET. As long as the intended offset from UTC is recorded (which it is
> > in a timestamptz) it should be fine.
> 
> I only really skimmed the entire response but this framing of how
> timestamptz stores results is wrong.  Once you've stored a timestamptz in
> PostgreSQL you no longer have any knowledge of the timezone.  If you truly
> need that you need to record that in a different field.  What you do know
> is that PostgreSQL has a known point-in-time in UTC and can give you back
> the same value expressed in any other timezone according to the rules in
> the timezone database.
> 
> Or, as written verbatim in the documentation:
> """
> For timestamp with time zone, the internally stored value is always in UTC
> (Universal Coordinated Time, traditionally known as Greenwich Mean Time,
> GMT). An input value that has an explicit time zone specified is converted
> to UTC using the appropriate offset for that time zone. If no time zone is
> stated in the input string, then it is assumed to be in the time zone
> indicated by the system's TimeZone parameter, and is converted to UTC using
> the offset for the timezone zone.
> """
> 
> https://www.postgresql.org/docs/12/datatype-datetime.html
> 
> David J.

You are misinterpreting the documentation, or the
documentation is incomplete/misleading at that
location. It doesn't just convert and store the time in
UTC. It stores the time in UTC and it also stores the
offset from UTC as determined by the time zone
specified on input (that section of the documentation
might not mention that fact but it is true
nonetheless).

I store localtimes in the database and always see the
offset when selecting it later. The timezone
information doesn't just disappear as you (or that
piece of documentation) might be suggesting.

If you don't believe me, try this:

  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;
  drop table example;

When I run that, I see:

  CREATE TABLE
  INSERT 0 1
                t               
  ------------------------------
   2020-04-16 17:12:33.71768+10
  (1 row)
  
  DROP TABLE

So the timezone is stored (or the offset is stored if
you prefer). Had it been daylight savings on that date
in Sydney, the offset would have been "+11". It's all
fine.

cheers,
raf