On 2021-09-21 12:37:49 -0700, Adrian Klaver wrote:
> On 9/21/21 11:45 AM, Peter J. Holzer wrote:
> > On 2021-09-21 13:43:46 -0400, Dave Cramer wrote:
> > > On Tue, 21 Sept 2021 at 13:40, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> > > On 2021-09-21 13:34:21 -0400, Dave Cramer wrote:
> > > > I would say this is a perspective thing. It's a timestamp with a time
> > > > zone from the client's perspective.
> > >
> > > I disagree. When I read back the value the original timezone is lost. So
> > > it clearly DOESN'T store the timestamp WITH the timezone.
> > >
> > >
> > > I never said it stored the timezone. I said that it has a timezone.
> >
> > The raison d’être of a database is to store data. If some data isn't
> > stored, the database doesn't have it, in my opinion.
> >
>
> But if you use timestamptz it does have it.
No.
> The data(timestamp) is stored with time zone UTC. From there you can
> reconstruct the timestamp at any time zone you want, given the clients
> needs. I'm not sure why where it started is important when people care
> how it is presented to them on retrieval.
Whether it is important or not is besides the point. It isn't there and
it cannot be reconstructed. If you need it, you have to store it
separately.
hjp=> set timezone to 'America/Chicago';
SET
hjp=> insert into t values('2021-03-01 04:15');
INSERT 0 1
hjp=> set timezone to 'Europe/Vienna';
SET
hjp=> insert into t values('2021-03-01 11:15');
INSERT 0 1
hjp=> select * from t;
╔════════════════════════╗
║ t ║
╟────────────────────────╢
║ 2021-03-01 11:15:00+01 ║
║ 2021-03-01 11:15:00+01 ║
╚════════════════════════╝
(2 rows)
These two values are completely indistinguishable. That's good for a
timestamp (they are the same time after all). But they are not a
"timestamp with time zone". The time zone is not part of the value but
of the environment.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"