Timestamp with vs without time zone.

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

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.

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.

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

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

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.

I never said it stored the timezone. I said that it has a timezone.

Dave Cramer
www.postgres.rocks

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

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

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

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