Re: timestamp and timestamptz

čt 16. 4. 2020 v 9:19 odesílatel raf <raf(at)raf(dot)org> napsal:

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

surely not. Postgres doesn't store offset. Every timestamptz is transalated
to UTC when it is parsed to binary form, and later translated to client
time zone when it is displayed.

Regards

Pavel

> cheers,
> raf
>
>
>