Thread: Timestamp with vs without time zone.

I am hoping to get some clarification on timestamp with time zone.

My understanding is that timestamp with time zone stores data in UTC
but displays it in your time zone.  Does this also work on queries? If
I query between noon and 2:00 PM on some date in time zone XYZ does pg
translate the query to UTC before sending it to the server?

To provide context I have the following situation.

I have a data file to import. All the dates in the time zone
pacific/auckland. My app reads the data , does some processing and
cleaning up and then saves it to the database.

The language I am using creates the time data type with the right time
zone. The processing is being done on a server which is on UTC, the
database server is also on UTC.  I am pretty sure the ORM isn't
appending "at time zone pacific/Auckland" to the data when it appends
it to the database.

So does the database know the timestamp is in auckland time when the
client is connecting from a server on UTC?

On Tue, 2021-09-21 at 18:00 +1200, Tim Uckun wrote:
> I am hoping to get some clarification on timestamp with time zone.
> 
> My understanding is that timestamp with time zone stores data in UTC
> but displays it in your time zone.

That is correct.
When a timestamp is rendered as string, it it shown in the time zone
specified by the current setting of the "timezone" parameter in your
database session.

> Does this also work on queries? If
> I query between noon and 2:00 PM on some date in time zone XYZ does pg
> translate the query to UTC before sending it to the server?

Yes.

> To provide context I have the following situation.
> 
> I have a data file to import. All the dates in the time zone
> pacific/auckland. My app reads the data , does some processing and
> cleaning up and then saves it to the database.
> 
> The language I am using creates the time data type with the right time
> zone. The processing is being done on a server which is on UTC, the
> database server is also on UTC.  I am pretty sure the ORM isn't
> appending "at time zone pacific/Auckland" to the data when it appends
> it to the database.
> 
> So does the database know the timestamp is in auckland time when the
> client is connecting from a server on UTC?

It does, but only if you set "timezone" appropriately in the database
session.  You could use ALTER ROLE to change the default setting for a
database user, but it might be best to set that from the application.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

It seems like it would be so much more useful if the timestamp with
time zone type actually stored the time zone in the record.


On Tue, Sep 21, 2021 at 7:25 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Tue, 2021-09-21 at 18:00 +1200, Tim Uckun wrote:
> > I am hoping to get some clarification on timestamp with time zone.
> >
> > My understanding is that timestamp with time zone stores data in UTC
> > but displays it in your time zone.
>
> That is correct.
> When a timestamp is rendered as string, it it shown in the time zone
> specified by the current setting of the "timezone" parameter in your
> database session.
>
> > Does this also work on queries? If
> > I query between noon and 2:00 PM on some date in time zone XYZ does pg
> > translate the query to UTC before sending it to the server?
>
> Yes.
>
> > To provide context I have the following situation.
> >
> > I have a data file to import. All the dates in the time zone
> > pacific/auckland. My app reads the data , does some processing and
> > cleaning up and then saves it to the database.
> >
> > The language I am using creates the time data type with the right time
> > zone. The processing is being done on a server which is on UTC, the
> > database server is also on UTC.  I am pretty sure the ORM isn't
> > appending "at time zone pacific/Auckland" to the data when it appends
> > it to the database.
> >
> > So does the database know the timestamp is in auckland time when the
> > client is connecting from a server on UTC?
>
> It does, but only if you set "timezone" appropriately in the database
> session.  You could use ALTER ROLE to change the default setting for a
> database user, but it might be best to set that from the application.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>

On Tue, 2021-09-21 at 19:35 +1200, Tim Uckun wrote:
> It seems like it would be so much more useful if the timestamp with
> time zone type actually stored the time zone in the record.

This has been requested before, and it would be closer to the intention
of the SQL standard, but I guess it won't happen.

For one, it would change on-disk storage, which would make it
impossible to use pg_upgrade.  It also would require timestamps to
occupy more than 8 bytes.

The best solution is probably to explicitly store the time zone as
an additional column.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

> It seems like it would be so much more useful if the timestamp with
> time zone type actually stored the time zone in the record.

Which one ?

Karsten

> > It seems like it would be so much more useful if the timestamp with
> > time zone type actually stored the time zone in the record.
>
> This has been requested before, and it would be closer to the intention
> of the SQL standard, but I guess it won't happen.
>
> For one, it would change on-disk storage, which would make it
> impossible to use pg_upgrade.  It also would require timestamps to
> occupy more than 8 bytes.
>
> The best solution is probably to explicitly store the time zone as
> an additional column.

Would that not be a perfect candidate for a fully fleshed out,
exemplary composite type ?

(but, then, yes it would beg the question what the TZ field of
 the composite is to actually mean...)

Karsten

One specified by the user.  Many date formats carry either an offset
or the time zone information.

On Tue, Sep 21, 2021 at 7:39 PM Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
>
> > It seems like it would be so much more useful if the timestamp with
> > time zone type actually stored the time zone in the record.
>
> Which one ?
>
> Karsten
>

> > > It seems like it would be so much more useful if the timestamp with
> > > time zone type actually stored the time zone in the record.
> >
> > Which one ?
> >
> One specified by the user.  Many date formats carry either an offset
> or the time zone information.

What would that TZ mean, exactly, especially in relation to the timestamp itself ?

Would the timestamp be stored as that TZ ?

Karsten

On 2021-09-21 09:39:59 +0200, Karsten Hilbert wrote:
> > It seems like it would be so much more useful if the timestamp with
> > time zone type actually stored the time zone in the record.
>
> Which one ?

To expand on that question a bit:

There are several formats to specify a time zone: By offset, by name
(several nomenclatures), etc.

For example, Karsten's mail had a timestamp of "2021-09-21 09:39:59
+0200". Thst's enough information to convert it to UTC, but not enough
for date arithmetic. For example what is
    '2021-09-21 09:39:59 +0200' + '2 months'::interval?

Should the result be '2021-11-21 09:39:59 +0200' or '2021-11-21 09:39:59
+0100'? I'm guessing that Karsten is in Germany, so it's probably the
latter. But to compute that you need to know that the timezone is
Europe/Berlin (or at least CET). Even that is not enough for dates in
the more distant future. The EU has decided to abolish DST (that should
have happened in 2020, but of course there was that little problem that
got in the way), but we don't know when that will happen and which
timezone Germany will choose. So for a date in e.g. 2025 we simply don't
know what the timezone offset will be.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Yes it would record the timestamp and then also record the time zone.
That way all the conversion functions would still work.

That's the way it works in the programming languages I use anyway.

On Tue, Sep 21, 2021 at 8:09 PM Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
>
> > > > It seems like it would be so much more useful if the timestamp with
> > > > time zone type actually stored the time zone in the record.
> > >
> > > Which one ?
> > >
> > One specified by the user.  Many date formats carry either an offset
> > or the time zone information.
>
> What would that TZ mean, exactly, especially in relation to the timestamp itself ?
>
> Would the timestamp be stored as that TZ ?
>
> Karsten

That's all true and I won't argue about the madness that is timezones
in the world. I am simply thinking it would be some sort of a struct
like thing which would store the numerical value of the time stamp and
also the time zone that time was recorded in.  Presumably everything
else is an insane calculation from there. What was the offset on that
day? I guess it depends on the daylight savings time. What would the
conversion to another time zone be? That would depend on the DST
settings on that day in both places.

Mankind can't agree on what side of the road to drive on, what the
electrical voltage should be at the wall, what those plugs should be,
how you should charge your phone or anything else for that matter so
there is no way of avoiding the insanity.  It's just that the phrase
"timestamp with time zone" would seem to indicate the time zone is
stored somewhere in there.


On Tue, Sep 21, 2021 at 8:44 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
> On 2021-09-21 09:39:59 +0200, Karsten Hilbert wrote:
> > > It seems like it would be so much more useful if the timestamp with
> > > time zone type actually stored the time zone in the record.
> >
> > Which one ?
>
> To expand on that question a bit:
>
> There are several formats to specify a time zone: By offset, by name
> (several nomenclatures), etc.
>
> For example, Karsten's mail had a timestamp of "2021-09-21 09:39:59
> +0200". Thst's enough information to convert it to UTC, but not enough
> for date arithmetic. For example what is
>     '2021-09-21 09:39:59 +0200' + '2 months'::interval?
>
> Should the result be '2021-11-21 09:39:59 +0200' or '2021-11-21 09:39:59
> +0100'? I'm guessing that Karsten is in Germany, so it's probably the
> latter. But to compute that you need to know that the timezone is
> Europe/Berlin (or at least CET). Even that is not enough for dates in
> the more distant future. The EU has decided to abolish DST (that should
> have happened in 2020, but of course there was that little problem that
> got in the way), but we don't know when that will happen and which
> timezone Germany will choose. So for a date in e.g. 2025 we simply don't
> know what the timezone offset will be.
>
>         hp
>
> --
>    _  | Peter J. Holzer    | Story must make more sense than reality.
> |_|_) |                    |
> | |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |       challenge!"

> It's just that the phrase
> "timestamp with time zone" would seem to indicate the time zone is
> stored somewhere in there.

Now, I can fully agree with _that_ :-)

Karsten

On 9/20/21 11:00 PM, Tim Uckun wrote:
> I am hoping to get some clarification on timestamp with time zone.
> 
> My understanding is that timestamp with time zone stores data in UTC
> but displays it in your time zone.  Does this also work on queries? If
> I query between noon and 2:00 PM on some date in time zone XYZ does pg
> translate the query to UTC before sending it to the server?
> 

> So does the database know the timestamp is in auckland time when the
> client is connecting from a server on UTC?

My question would be why does it matter? The whole purpose of 
timestamptz is that you know it is stored as UTC, from there you can 
transform to whatever time zone you want.



-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 9/20/21 11:00 PM, Tim Uckun wrote:
>> I am hoping to get some clarification on timestamp with time zone.
>> 
>> My understanding is that timestamp with time zone stores data in UTC
>> but displays it in your time zone.  Does this also work on queries? If
>> I query between noon and 2:00 PM on some date in time zone XYZ does pg
>> translate the query to UTC before sending it to the server?
>> 
>> So does the database know the timestamp is in auckland time when the
>> client is connecting from a server on UTC?

It might be more clear if you realize that there is no client-side logic
involved here.  The rotation to/from UTC happens in timestamptz_in or
timestamptz_out, based on the server's TimeZone setting.

It's incumbent on clients to set TimeZone correctly if they want
unlabeled timestamps to be interpreted in a particular zone.

            regards, tom lane

On 2021-09-21 20:50:44 +1200, Tim Uckun wrote:
> That's all true and I won't argue about the madness that is timezones
> in the world. I am simply thinking it would be some sort of a struct
> like thing which would store the numerical value of the time stamp and
> also the time zone that time was recorded in.  Presumably everything
> else is an insane calculation from there. What was the offset on that
> day? I guess it depends on the daylight savings time. What would the
> conversion to another time zone be? That would depend on the DST
> settings on that day in both places.

Yes, but HOW IS THAT TIME ZONE STORED?

As a user you can say "I don't care, just make it work somehow".

But as a developer you have to decide on a specific way. And as a
database developer in particular you would have to choose a way which
works for almost everybody.

And that's the problem because ...

> Mankind can't agree on what side of the road to drive on, what the
> electrical voltage should be at the wall, what those plugs should be,
> how you should charge your phone or anything else for that matter

... people have different needs and it would be difficult to satisfy
them all.

Simply storing an offset from UTC is simple, fast, doesn't take much
space - but it would be almost as misleading as the current state. A
simple offset is not a time zone.

Storing the IANA timezone names (e.g. 'Europe/Vienna') would store an
identifier for what most people think of as a time zone - but that takes
a lot of space, it needs a lookup for almost any operation and worst of
all, you couldn't index such a column (at least not with a btree index)
because the comparison functions aren't stable.

You could use a numeric indentifier instead of the name, that would take
less space but wouldn't solve the other problems (and add the problem
that now you have just added another mapping which you need to maintain).

There are other ways, but I'm sure they all have some pros and some
cons. None will be perfect.

So I don't think there is an obvious (or even non-obvious, but clearly
good) way for the PostgreSQL developers to add a real "timestamp with
timezone" type.

As an application developer however, you can define a compound type (or
just use two or three columns together) which satisfies the needs of
your specific application.

> It's just that the phrase "timestamp with time zone" would seem to
> indicate the time zone is stored somewhere in there.

I absolutely agree. Calling a type which doesn't include a timezone
"timestamp with timezone" is - how do I put this? - more than just
weird. "timestamp without timezone" should be called "local timestamp
with unspecified timezone" and "timestamp with timezone" should be
called "global timestamp without timezone". However, those aren't SQL
names.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 2021-09-21 13:34:21 -0400, Dave Cramer wrote:
> On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>     On 2021-09-21 20:50:44 +1200, Tim Uckun wrote:
>     > It's just that the phrase "timestamp with time zone" would seem to
>     > indicate the time zone is stored somewhere in there.
>
>     I absolutely agree. Calling a type which doesn't include a timezone
>     "timestamp with timezone" is - how do I put this? - more than just
>     weird. "timestamp without timezone" should be called "local timestamp
>     with unspecified timezone" and "timestamp with timezone" should be
>     called "global timestamp without timezone". However, those aren't SQL
>     names.
>
>
>
> 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.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

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:
>     > On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>     >     On 2021-09-21 20:50:44 +1200, Tim Uckun wrote:
>     >     Calling a type which doesn't include a timezone
>     >     "timestamp with timezone" is - how do I put this? - more than just
>     >     weird.
>     >
>     > 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.

As a different example, I can store a number with 15 decimal digits in a float4
and I can get 15 decimal digits out again:

hjp=> create table t (f float4);
CREATE TABLE
hjp=> insert into t(f) values(1.23456789012345);
INSERT 0 1
hjp=> select f::float8::numeric from t;
╔══════════════════╗
║        f         ║
╟──────────────────╢
║ 1.23456788063049 ║
╚══════════════════╝
(1 row)

But those digits aren't the same I stored. So a float4 doesn't "have" 15
decimal digits of accuracy. Not even 8, although in this specific case
the first 8 digits happen to be correct.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

[Can y'all please trim your quotes? Having to scroll down 80+ lines to
get to the content is cumbersome]

On 2021-09-21 13:17:39 -0500, FWS Neil wrote:
> A timestamp cannot have a time zone and be a valid timestamp.
>
> Let me explain.
>
> A timestamp is a single time that exists in the world.

Correct.

> For example March 1, 2021, 4:15 am is a timestamp.

This is NOT a timestamp, because it is not a single time that exists in
the world. It is about 30 different times that exist in the world.

You can turn it into a timestamp by adding timezone information:

March 1 2021, 4:15 am, America/Chicago

Now it's a single time, equivalent to 2021-03-01T10:15:00Z.

However, if you don't have to store the timezone if you have some other
way to unambiguously specify the time, e.g. by always using UTC or by
storing seconds since an epoch.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

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:
>>      > On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>>      >     On 2021-09-21 20:50:44 +1200, Tim Uckun wrote:
>>      >     Calling a type which doesn't include a timezone
>>      >     "timestamp with timezone" is - how do I put this? - more than just
>>      >     weird.
>>      >
>>      > 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. 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.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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!"

On 9/21/21 11:17 AM, FWS Neil wrote:
> 
> 
>> On Sep 21, 2021, at 12:34 PM, Dave Cramer <davecramer@postgres.rocks 
>> <mailto:davecramer@postgres.rocks>> wrote:
>> On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer <hjp-pgsql@hjp.at 
>> <mailto:hjp-pgsql@hjp.at>> wrote:
>>
>>     On 2021-09-21 20:50:44 +1200, Tim Uckun wrote:
>>     > That's all true and I won't argue about the madness that is
>>     timezones
>>     > in the world. I am simply thinking it would be some sort of a struct
>>     > like thing which would store the numerical value of the time
>>     stamp and
>>     > also the time zone that time was recorded in.  Presumably everything
>>     > else is an insane calculation from there. What was the offset on
>>     that
>>     > day? I guess it depends on the daylight savings time. What would the
>>     > conversion to another time zone be? That would depend on the DST
>>     > settings on that day in both places.
>>
>>     Yes, but HOW IS THAT TIME ZONE STORED?
>>
>>     As a user you can say "I don't care, just make it work somehow".
>>
>>     But as a developer you have to decide on a specific way. And as a
>>     database developer in particular you would have to choose a way which
>>     works for almost everybody.
>>
>>     And that's the problem because ...
>>
>>     > Mankind can't agree on what side of the road to drive on, what the
>>     > electrical voltage should be at the wall, what those plugs
>>     should be,
>>     > how you should charge your phone or anything else for that matter
>>
>>     ... people have different needs and it would be difficult to satisfy
>>     them all.
>>
>>     Simply storing an offset from UTC is simple, fast, doesn't take much
>>     space - but it would be almost as misleading as the current state. A
>>     simple offset is not a time zone.
>>
>>     Storing the IANA timezone names (e.g. 'Europe/Vienna') would store an
>>     identifier for what most people think of as a time zone - but that
>>     takes
>>     a lot of space, it needs a lookup for almost any operation and
>>     worst of
>>     all, you couldn't index such a column (at least not with a btree
>>     index)
>>     because the comparison functions aren't stable.
>>
>>     You could use a numeric indentifier instead of the name, that
>>     would take
>>     less space but wouldn't solve the other problems (and add the problem
>>     that now you have just added another mapping which you need to
>>     maintain).
>>
>>     There are other ways, but I'm sure they all have some pros and some
>>     cons. None will be perfect.
>>
>>     So I don't think there is an obvious (or even non-obvious, but clearly
>>     good) way for the PostgreSQL developers to add a real "timestamp with
>>     timezone" type.
>>
>>     As an application developer however, you can define a compound
>>     type (or
>>     just use two or three columns together) which satisfies the needs of
>>     your specific application.
>>
>>     > It's just that the phrase "timestamp with time zone" would seem to
>>     > indicate the time zone is stored somewhere in there.
>>
>>     I absolutely agree. Calling a type which doesn't include a timezone
>>     "timestamp with timezone" is - how do I put this? - more than just
>>     weird. "timestamp without timezone" should be called "local timestamp
>>     with unspecified timezone" and "timestamp with timezone" should be
>>     called "global timestamp without timezone". However, those aren't SQL
>>     names.
>>
>>
>> I would say this is a perspective thing. It's a timestamp with a time 
>> zone from the client's perspective.
> 
> A timestamp cannot have a time zone and be a valid timestamp.
> 
> Let me explain.
> 
> A timestamp is a single time that exists in the world.  For example 
> March 1, 2021, 4:15 am is a timestamp.
> 
> If you add a time zone (other than UTC) then a time stamp is not always 
> a single time that exists in the world.

You have the above backwards. A time zone locks the timestamp to a 
single point in time.

> 
> For example in the spring using time zone American/Chicago, on April 14, 
> 2021 the time zone time changes at 2am to become 3am.  The time April 
> 14, 2021, 2:30 am simply does not exists.  And therefore cannot be a 
> timestamp.  Apple’s APIs will by default automatically change 2:30am to 
> 3:00am.  Is that correct?  Or should it change to 3:30am?  Apple has the 
> option for the latter, but the APIs don’t work.

In a sense it does, it becomes 3:30 am CDT.

The DST change was actually 03/14/2021.

An example for my timezone US/Pacific:

test(5432)=# select '03/13/2021 2:30 am'::timestamptz;
       timestamptz
------------------------
  2021-03-13 02:30:00-08
(1 row)

test(5432)=# select '03/14/2021 2:30 am'::timestamptz;
       timestamptz
------------------------
  2021-03-14 03:30:00-07


> 
> In the fall it is even worse.  Using time zone America/Chicago, on 
> November 7, 2021, 1:30 am occurs twice.  That does not work as a 
> timestamp.  Which one do you use, the early one or the late one. 
>   Apple’s APIs give you a choice.

It occurs in two different time zones CDT then CST which makes it a 
different time in each case.

> 
> The point being that people do expect to see times in local time, but 
> the only real timestamp is UTC and I can’t ever imagine a need to store 
> time zone information related to a timestamp.  If you need to store the 
> location that data originated from, then store the location or the Time 
> Zone, but it should not be connected to the timestamp.  Location data is 
> completely different than time data.
> 
> Neil
> www.fairwindsoft.com <http://www.fairwindsoft.com>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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.

On 2021-09-21 12:58:13 -0700, David G. Johnston wrote:
> On Tuesday, September 21, 2021, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>     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.
>
>
> No, it’s inherent to the data type itself.  The data type has an implied time
> zone of UTC.  That is a reasonable definition of “with” in my book.

If you can store only a single value that's 0 bits of information (which
is exactly the storage used).

But I'm going to stop arguing here. I'm obviously not able to convince
you and you aren't going to convince me either.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

 From my experience, and some might disagree, I prefer to do db stores 
purely in UTC and handle timezones in ORM or client side.

The only time I actually needed to store timezone information in a 
dedicated column is when needing to convey that information to the end 
user, for example "your plane will arrive at this date and time in this 
destination timezone". The majority of other cases are just a 
localization issue and don't require you to store the timezone info.

Having to rely on database to muck around with timezones or doing it in 
session settings (which some advocate) is just asking for trouble in my 
opinion.


On 9/21/21 9:35 AM, Tim Uckun wrote:
> It seems like it would be so much more useful if the timestamp with
> time zone type actually stored the time zone in the record.
>
>
> On Tue, Sep 21, 2021 at 7:25 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>> On Tue, 2021-09-21 at 18:00 +1200, Tim Uckun wrote:
>>> I am hoping to get some clarification on timestamp with time zone.
>>>
>>> My understanding is that timestamp with time zone stores data in UTC
>>> but displays it in your time zone.
>> That is correct.
>> When a timestamp is rendered as string, it it shown in the time zone
>> specified by the current setting of the "timezone" parameter in your
>> database session.
>>
>>> Does this also work on queries? If
>>> I query between noon and 2:00 PM on some date in time zone XYZ does pg
>>> translate the query to UTC before sending it to the server?
>> Yes.
>>
>>> To provide context I have the following situation.
>>>
>>> I have a data file to import. All the dates in the time zone
>>> pacific/auckland. My app reads the data , does some processing and
>>> cleaning up and then saves it to the database.
>>>
>>> The language I am using creates the time data type with the right time
>>> zone. The processing is being done on a server which is on UTC, the
>>> database server is also on UTC.  I am pretty sure the ORM isn't
>>> appending "at time zone pacific/Auckland" to the data when it appends
>>> it to the database.
>>>
>>> So does the database know the timestamp is in auckland time when the
>>> client is connecting from a server on UTC?
>> It does, but only if you set "timezone" appropriately in the database
>> session.  You could use ALTER ROLE to change the default setting for a
>> database user, but it might be best to set that from the application.
>>
>> Yours,
>> Laurenz Albe
>> --
>> Cybertec | https://www.cybertec-postgresql.com
>>
>

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, September 21, 2021, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>> 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.

> No, it’s inherent to the data type itself.  The data type has an implied
> time zone of UTC.  That is a reasonable definition of “with” in my book.

The SQL standard is pretty clear that they intend "timestamp with time
zone" to include a time zone represented by a specific UTC offset.
We didn't implement it that way, which is not great, but with
twenty-some years of history there it seems unlikely that we'll change.
Especially since the SQL spec hasn't exactly covered itself with glory
in terms of providing *useful* datetime types.  (Anybody ever found a
use for timetz?)

I think there is plenty of application for timestamps that actually
include (civil) time zones.  Calendaring, for example.  If I make an
appointment to see a friend at 2PM some months from now, it's
understood that that's in the local time zone; if some lawmakers take
it on themselves to fool with the DST rules before then, we're still
going to meet at 2PM local time.  And it'd be useful to know whether
that now conflicts with appointments defined by reference to some
other zone, so the easy way of "assume it's all local time" doesn't
cut it.

This same example demonstrates why the SQL spec's idea of "timestamp
with time zone" doesn't cut it.  You really need some DST-aware notion
of timezone identity.  But, as already covered upthread, the can of
worms that that opens is so large that nobody has wanted to try to
tackle it as a primitive Postgres datatype.

            regards, tom lane

FWS Neil <neil@fairwindsoft.com> writes:

>  On Sep 21, 2021, at 12:34 PM, Dave Cramer <davecramer@postgres.rocks> wrote:
>  On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
>  On 2021-09-21 20:50:44 +1200, Tim Uckun wrote:
>  > That's all true and I won't argue about the madness that is timezones
>  > in the world. I am simply thinking it would be some sort of a struct
>  > like thing which would store the numerical value of the time stamp and
>  > also the time zone that time was recorded in.  Presumably everything
>  > else is an insane calculation from there. What was the offset on that
>  > day? I guess it depends on the daylight savings time. What would the
>  > conversion to another time zone be? That would depend on the DST
>  > settings on that day in both places.
>
>  Yes, but HOW IS THAT TIME ZONE STORED?
>
>  As a user you can say "I don't care, just make it work somehow".
>
>  But as a developer you have to decide on a specific way. And as a
>  database developer in particular you would have to choose a way which
>  works for almost everybody.
>
>  And that's the problem because ...
>
>  > Mankind can't agree on what side of the road to drive on, what the
>  > electrical voltage should be at the wall, what those plugs should be,
>  > how you should charge your phone or anything else for that matter
>
>  ... people have different needs and it would be difficult to satisfy
>  them all.
>
>  Simply storing an offset from UTC is simple, fast, doesn't take much
>  space - but it would be almost as misleading as the current state. A
>  simple offset is not a time zone.
>
>  Storing the IANA timezone names (e.g. 'Europe/Vienna') would store an
>  identifier for what most people think of as a time zone - but that takes
>  a lot of space, it needs a lookup for almost any operation and worst of
>  all, you couldn't index such a column (at least not with a btree index)
>  because the comparison functions aren't stable.
>
>  You could use a numeric indentifier instead of the name, that would take
>  less space but wouldn't solve the other problems (and add the problem
>  that now you have just added another mapping which you need to maintain).
>
>  There are other ways, but I'm sure they all have some pros and some
>  cons. None will be perfect.
>
>  So I don't think there is an obvious (or even non-obvious, but clearly
>  good) way for the PostgreSQL developers to add a real "timestamp with
>  timezone" type.
>
>  As an application developer however, you can define a compound type (or
>  just use two or three columns together) which satisfies the needs of
>  your specific application.
>
>  > It's just that the phrase "timestamp with time zone" would seem to
>  > indicate the time zone is stored somewhere in there.
>
>  I absolutely agree. Calling a type which doesn't include a timezone
>  "timestamp with timezone" is - how do I put this? - more than just
>  weird. "timestamp without timezone" should be called "local timestamp
>  with unspecified timezone" and "timestamp with timezone" should be
>  called "global timestamp without timezone". However, those aren't SQL
>  names.
>
>  I would say this is a perspective thing. It's a timestamp with a time zone from the client's perspective.
>
> A timestamp cannot have a time zone and be a valid timestamp.
>
> Let me explain.
>
> A timestamp is a single time that exists in the world.  For example March 1, 2021, 4:15 am is a timestamp.
>
> If you add a time zone (other than UTC) then a time stamp is not always a single time that exists in the world.
>
> For example in the spring using time zone American/Chicago, on April 14, 2021 the time zone time changes at 2am to
become
> 3am.  The time April 14, 2021, 2:30 am simply does not exists.  And therefore cannot be a timestamp.  Apple’s APIs
willby 
> default automatically change 2:30am to 3:00am.  Is that correct?  Or should it change to 3:30am?  Apple has the
optionfor the 
> latter, but the APIs don’t work.
>
> In the fall it is even worse.  Using time zone America/Chicago, on November 7, 2021, 1:30 am occurs twice.  That does
notwork 
> as a timestamp.  Which one do you use, the early one or the late one.  Apple’s APIs give you a choice.
>
> The point being that people do expect to see times in local time, but the only real timestamp is UTC and I can’t ever
imaginea 
> need to store time zone information related to a timestamp.  If you need to store the location that data originated
from,then 
> store the location or the Time Zone, but it should not be connected to the timestamp.  Location data is completely
differentthan 
> time data.
>

+1. This is the key point often overlooked. To make matters even more
complex, the daylight savings switch over dates can change, often at the
whim of politicians. For example, the daylight savings time has been
changed in Australia because of major events (such as the Olympics). As
soon as you bring time zones into the mix, any calculations based on
differences in dates must now also take into account timezone switchover
(because you gain/loose 1 hour) AND you need to verify what the date was
for each year in your time interval. This significantly complicates such
calculations.

Things can become even more complicated when server and clients are in
different time zones - which do you use? The time zone of the server or
the time zone of the client? In some cases it will be an obvious choice,
but in others perhaps not. Will all the developers then have the same
view/understanding? What will be the right thing to do when clients fail
to provide tz info in submitted data? Default to server tz or client tz?

On the other hand, if all timestamps are in UTC, you completely avoid
this complication and can just map any result to local timezone if
required. Even better, the client can decide what tz they want the
values to be displayed in. Any calculations can be performed in UTC
without the need to reference any DST data.

Timestamps and Time Zones are a misleading concept. A timestamp is a
point in time best represented without any ambiguity and the best
representation for machines is UTC. Time zones are for humans and are really
only needed when humans need to understand or map the timestamp to a
representation relative to them and is therefore most often best left to
the client layer.

Related to this current discussion and exchange of ideas... is there a best practice for retrieving data in such a way as the rows are localized to a timezone for where/group by purposes. That is, if I have a table which has events, but those events belong to a tenant or some entity that has a location which implies a timezone (or at least an offset), is there a best way to write a query similar to the below? Please forgive and overlook if there is some obvious syntax error, as this is just a quick and dirty example. Might it make sense to store a "localized" version of the timestamp *without* timezone on the event record such that an index can be used for fast retrieval and even grouping?

select
date_trunc( 'month', e.event_datetime AT TIMEZONE t.time_zone_name ) AS event_date,

count( e.id )
from events AS e
join tenants AS t ON t.id = e.tenant_id

where e.event_datetime AT TIMEZONE t.time_zone_name >= '01/01/2021'::DATE AND e.event_datetime AT TIMEZONE t.time_zone_name < '09/01/2021'::DATE;

This is an interesting case. A simplified query example would be to "give me all events for this year".

I am not sure what the cost of shifting UTC is, probably not much, but depending on use case it might make sense to deconstruct into date and time for query optimization.

I'll add another layer of complication.

You have a database server hosted in Australia, and that's also where
your web server and api server is. You have customers all over the
world though so you set up additional API servers in Europe, USA,
Japan etc.

A korean user will fetch you single page app as static HTML from S3
with cloudfront. It will hit your japanese API server,  which will
fetch the data from your japanese read only replica with the master
being in Australia.

The master DB  writes the records has to know your end user is in
Korea somehow so you have to carry that time zone all the way across
those tiers.

To me the ideal solution would be to have a compound object which has
the time zone in it. This object gets passed through the tiers and end
up at the database where it's stored.




On Wed, Sep 22, 2021 at 1:52 PM Tim Cross <theophilusx@gmail.com> wrote:
>
>
> FWS Neil <neil@fairwindsoft.com> writes:
>
> >  On Sep 21, 2021, at 12:34 PM, Dave Cramer <davecramer@postgres.rocks> wrote:
> >  On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> >
> >  On 2021-09-21 20:50:44 +1200, Tim Uckun wrote:
> >  > That's all true and I won't argue about the madness that is timezones
> >  > in the world. I am simply thinking it would be some sort of a struct
> >  > like thing which would store the numerical value of the time stamp and
> >  > also the time zone that time was recorded in.  Presumably everything
> >  > else is an insane calculation from there. What was the offset on that
> >  > day? I guess it depends on the daylight savings time. What would the
> >  > conversion to another time zone be? That would depend on the DST
> >  > settings on that day in both places.
> >
> >  Yes, but HOW IS THAT TIME ZONE STORED?
> >
> >  As a user you can say "I don't care, just make it work somehow".
> >
> >  But as a developer you have to decide on a specific way. And as a
> >  database developer in particular you would have to choose a way which
> >  works for almost everybody.
> >
> >  And that's the problem because ...
> >
> >  > Mankind can't agree on what side of the road to drive on, what the
> >  > electrical voltage should be at the wall, what those plugs should be,
> >  > how you should charge your phone or anything else for that matter
> >
> >  ... people have different needs and it would be difficult to satisfy
> >  them all.
> >
> >  Simply storing an offset from UTC is simple, fast, doesn't take much
> >  space - but it would be almost as misleading as the current state. A
> >  simple offset is not a time zone.
> >
> >  Storing the IANA timezone names (e.g. 'Europe/Vienna') would store an
> >  identifier for what most people think of as a time zone - but that takes
> >  a lot of space, it needs a lookup for almost any operation and worst of
> >  all, you couldn't index such a column (at least not with a btree index)
> >  because the comparison functions aren't stable.
> >
> >  You could use a numeric indentifier instead of the name, that would take
> >  less space but wouldn't solve the other problems (and add the problem
> >  that now you have just added another mapping which you need to maintain).
> >
> >  There are other ways, but I'm sure they all have some pros and some
> >  cons. None will be perfect.
> >
> >  So I don't think there is an obvious (or even non-obvious, but clearly
> >  good) way for the PostgreSQL developers to add a real "timestamp with
> >  timezone" type.
> >
> >  As an application developer however, you can define a compound type (or
> >  just use two or three columns together) which satisfies the needs of
> >  your specific application.
> >
> >  > It's just that the phrase "timestamp with time zone" would seem to
> >  > indicate the time zone is stored somewhere in there.
> >
> >  I absolutely agree. Calling a type which doesn't include a timezone
> >  "timestamp with timezone" is - how do I put this? - more than just
> >  weird. "timestamp without timezone" should be called "local timestamp
> >  with unspecified timezone" and "timestamp with timezone" should be
> >  called "global timestamp without timezone". However, those aren't SQL
> >  names.
> >
> >  I would say this is a perspective thing. It's a timestamp with a time zone from the client's perspective.
> >
> > A timestamp cannot have a time zone and be a valid timestamp.
> >
> > Let me explain.
> >
> > A timestamp is a single time that exists in the world.  For example March 1, 2021, 4:15 am is a timestamp.
> >
> > If you add a time zone (other than UTC) then a time stamp is not always a single time that exists in the world.
> >
> > For example in the spring using time zone American/Chicago, on April 14, 2021 the time zone time changes at 2am to
become
> > 3am.  The time April 14, 2021, 2:30 am simply does not exists.  And therefore cannot be a timestamp.  Apple’s APIs
willby 
> > default automatically change 2:30am to 3:00am.  Is that correct?  Or should it change to 3:30am?  Apple has the
optionfor the 
> > latter, but the APIs don’t work.
> >
> > In the fall it is even worse.  Using time zone America/Chicago, on November 7, 2021, 1:30 am occurs twice.  That
doesnot work 
> > as a timestamp.  Which one do you use, the early one or the late one.  Apple’s APIs give you a choice.
> >
> > The point being that people do expect to see times in local time, but the only real timestamp is UTC and I can’t
everimagine a 
> > need to store time zone information related to a timestamp.  If you need to store the location that data originated
from,then 
> > store the location or the Time Zone, but it should not be connected to the timestamp.  Location data is completely
differentthan 
> > time data.
> >
>
> +1. This is the key point often overlooked. To make matters even more
> complex, the daylight savings switch over dates can change, often at the
> whim of politicians. For example, the daylight savings time has been
> changed in Australia because of major events (such as the Olympics). As
> soon as you bring time zones into the mix, any calculations based on
> differences in dates must now also take into account timezone switchover
> (because you gain/loose 1 hour) AND you need to verify what the date was
> for each year in your time interval. This significantly complicates such
> calculations.
>
> Things can become even more complicated when server and clients are in
> different time zones - which do you use? The time zone of the server or
> the time zone of the client? In some cases it will be an obvious choice,
> but in others perhaps not. Will all the developers then have the same
> view/understanding? What will be the right thing to do when clients fail
> to provide tz info in submitted data? Default to server tz or client tz?
>
> On the other hand, if all timestamps are in UTC, you completely avoid
> this complication and can just map any result to local timezone if
> required. Even better, the client can decide what tz they want the
> values to be displayed in. Any calculations can be performed in UTC
> without the need to reference any DST data.
>
> Timestamps and Time Zones are a misleading concept. A timestamp is a
> point in time best represented without any ambiguity and the best
> representation for machines is UTC. Time zones are for humans and are really
> only needed when humans need to understand or map the timestamp to a
> representation relative to them and is therefore most often best left to
> the client layer.
>
>

Tim Uckun <timuckun@gmail.com> writes:

> I'll add another layer of complication.
>
> You have a database server hosted in Australia, and that's also where
> your web server and api server is. You have customers all over the
> world though so you set up additional API servers in Europe, USA,
> Japan etc.
>
> A korean user will fetch you single page app as static HTML from S3
> with cloudfront. It will hit your japanese API server,  which will
> fetch the data from your japanese read only replica with the master
> being in Australia.
>
> The master DB  writes the records has to know your end user is in
> Korea somehow so you have to carry that time zone all the way across
> those tiers.
>
> To me the ideal solution would be to have a compound object which has
> the time zone in it. This object gets passed through the tiers and end
> up at the database where it's stored.
>

I wouldn't do it that way. I would have all timestamps in UTC. If the
location of the client is necessary for some business process, then I
would explicitly record that information rather than rely on time zone
data to derive location.

I've also found having all timestamps in UTC makes any analysis and bug
tracing much easier. Nothing worse then when you need to look at
timestamps from different components of your architecture spread over
different time zones where you also have to convert the timestamps to a
common/consistent time zone for comparison and sequencing purposes. 

On 22/09/21 20:11, Tim Uckun wrote:
> I'll add another layer of complication.
>
> You have a database server hosted in Australia, and that's also where
> your web server and api server is. You have customers all over the
> world though so you set up additional API servers in Europe, USA,
> Japan etc.
>
> A korean user will fetch you single page app as static HTML from S3
> with cloudfront. It will hit your japanese API server,  which will
> fetch the data from your japanese read only replica with the master
> being in Australia.
>
> The master DB  writes the records has to know your end user is in
> Korea somehow so you have to carry that time zone all the way across
> those tiers.
>
> To me the ideal solution would be to have a compound object which has
> the time zone in it. This object gets passed through the tiers and end
> up at the database where it's stored.
>
[...]

Please don't top post!  Bottom post like I am.

I imagine the compound object, call it timestamporigin (timestampo?)) 
would have 2 components:

 1. GMT time
 2. Offset hours & minutes
 3. client time zone code

This was means:

 1. you could calculate accurate time differences even between 2
    timestampo's regardless of which time zone they came from
 2. you know the correct local time it was made
 3. you know the time zone it came from
 4. it would still be correct even if the machine's, or the server's,
    time zone file was updated and the changes applied retroactively.


Cheers,
Gavin

Am Wed, Sep 22, 2021 at 08:11:50PM +1200 schrieb Tim Uckun:

> A korean user will fetch you single page app as static HTML from S3
> with cloudfront. It will hit your japanese API server,  which will
> fetch the data from your japanese read only replica with the master
> being in Australia.
>
> The master DB  writes the records has to know your end user is in
> Korea somehow so you have to carry that time zone all the way across
> those tiers.

One does not: as soon as the real-world concept of
point-in-time hits the machine it gets converted to UTC. When
it leaves the machine (towards user consumption) it gets
converted to whatever is desired.

    UTC = UTF8

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

On Wed, Sep 22, 2021 at 12:44 AM cen <cen.is.imba@gmail.com> wrote:

On 21. 09. 21 23:34, Michael Lewis wrote:

Related to this current discussion and exchange of ideas... is there a best practice for retrieving data in such a way as the rows are localized to a timezone for where/group by purposes. That is, if I have a table which has events, but those events belong to a tenant or some entity that has a location which implies a timezone (or at least an offset), is there a best way to write a query similar to the below? Please forgive and overlook if there is some obvious syntax error, as this is just a quick and dirty example. Might it make sense to store a "localized" version of the timestamp *without* timezone on the event record such that an index can be used for fast retrieval and even grouping?

select
date_trunc( 'month', e.event_datetime AT TIMEZONE t.time_zone_name ) AS event_date,

count( e.id )
from events AS e
join tenants AS t ON t.id = e.tenant_id

where e.event_datetime AT TIMEZONE t.time_zone_name >= '01/01/2021'::DATE AND e.event_datetime AT TIMEZONE t.time_zone_name < '09/01/2021'::DATE;

This is an interesting case. A simplified query example would be to "give me all events for this year".

I am not sure what the cost of shifting UTC is, probably not much, but depending on use case it might make sense to deconstruct into date and time for query optimization.

Does that solve anything? My concern is that for the events at the begin and end of the where (or group by) we may want to include them or exclude them depending on the time in that location. If I want to know how many events happen between 8am and 5pm for each location in California and New York for example, I'm not certain if a second timestamp that is "localized" should be stored, or if there is some better way to achieve that goal. At least for some data in the evening, the date value will be different between UTC and local timezone for that location. Just setting my session to an east coast equivalent timezone when I pull the data still doesn't give me "what are all the events that happened in California" accurately. The goal is that the data is accurate for that location. Not as much a concern if there are only a few hours apart, but if the user is running a report across properties all over the world... what's the proper way to ensure each date means the day for that location?

On Tue, Sep 21, 2021 at 05:49:21PM -0400, Tom Lane wrote:
> I think there is plenty of application for timestamps that actually
> include (civil) time zones.  Calendaring, for example.  If I make an
> appointment to see a friend at 2PM some months from now, it's
> understood that that's in the local time zone; if some lawmakers take
> it on themselves to fool with the DST rules before then, we're still
> going to meet at 2PM local time.  And it'd be useful to know whether
> that now conflicts with appointments defined by reference to some
> other zone, so the easy way of "assume it's all local time" doesn't
> cut it.

I wrote a blog about this:

    https://momjian.us/main/blogs/pgblog/2017.html#September_27_2017

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.