Re: timestamp and timestamptz

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

On Thu, Apr 16, 2020 at 5:19 PM raf <raf(at)raf(dot)org> wrote:

> David G. Johnston wrote:
>
> > On Wed, Apr 15, 2020 at 4:53 PM raf <raf(at)raf(dot)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
>
>
>