Clarifying "timestamp with time zone"

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Clarifying "timestamp with time zone"

jfinzel
Hello!

We often prefer to use timestamptz or "timestamp with time zone" in our environment because of its actually storing "objective time" with respect to UTC.  But in my own work experience, I have scarcely encountered a case where business users, and software engineers, do not actually think it means the opposite.

When I say "timestamp with time zone", people think the data is saved in a specific time zone, whereas in reality, the opposite is true.  It is really more like "timestamp UTC" or you even could say "timestamp at UTC".  When you query this of course, then it shows you the time offset based on your client timezone setting.

I do believe this is part of the SQL standard, but I have found that it creates great confusion.  I think many devs choose timestamp instead of timestamptz because they don't really understand that timestamptz gives you UTC time storage built-in.

That of course means that if you have multiple servers that run in a different time zone, and you want to replicate that data to a centralized location, you can easily figure out what objective time a record changed, for instance, not knowing anything about what time zone the source system is in.

So it seems to me that "timestamp with time zone" is a misnomer in a big way, and perhaps it's worth at least clarifying the docs about this, or even renaming the type or providing an aliased type that means the same thing, something like timestamputc.  Maybe I'm crazy but I would appreciate any feedback on this and how easily it confuses.

Thanks,
Jeremy
Reply | Threaded
Open this post in threaded view
|

Re: Clarifying "timestamp with time zone"

David G Johnston
On Fri, Jun 15, 2018 at 12:24 PM, Jeremy Finzel <[hidden email]> wrote:
So it seems to me that "timestamp with time zone" is a misnomer in a big way, and perhaps it's worth at least clarifying the docs about this, or even renaming the type or providing an aliased type that means the same thing, something like timestamputc.  Maybe I'm crazy but I would appreciate any feedback on this and how easily it confuses.

​If you present a doc patch that people agree improves the situation it will likely be committed.

I don't see any effort in the data type​ area being worthwhile though.  Renaming is impossible and an alias is probably just going to confuse matters even further by giving people one more thing they need to understand.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Clarifying "timestamp with time zone"

Tom Lane-2
In reply to this post by jfinzel
Jeremy Finzel <[hidden email]> writes:
> We often prefer to use timestamptz or "timestamp with time zone" in our
> environment because of its actually storing "objective time" with respect
> to UTC.  But in my own work experience, I have scarcely encountered a case
> where business users, and software engineers, do not actually think it
> means the opposite.

Yeah, it's confusing :-(.

> I do believe this is part of the SQL standard,

Actually not, or at least the standard thinks the type should behave
differently from this.  They think it should store a local timestamp and
then separately a GMT offset.  The way it works in PG was dreamed up ~20
years ago by Tom Lockhart, and in retrospect it was not a great idea
for him to have used the SQL-standard type name for something with
not-SQL-standard behavior.

However, at this point, it's not clear how we could change it without
causing truly enormous backwards-compatibility pain.  If it were a
fringe-usage type, maybe we could get away with a renaming, but it's
certainly not that.

> So it seems to me that "timestamp with time zone" is a misnomer in a big
> way, and perhaps it's worth at least clarifying the docs about this,

Don't the docs describe the behavior pretty clearly already?

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: Clarifying "timestamp with time zone"

Adrian Klaver-4
In reply to this post by jfinzel
On 06/15/2018 12:24 PM, Jeremy Finzel wrote:

> Hello!
>
> We often prefer to use timestamptz or "timestamp with time zone" in our
> environment because of its actually storing "objective time" with
> respect to UTC.  But in my own work experience, I have scarcely
> encountered a case where business users, and software engineers, do not
> actually think it means the opposite.
>
> When I say "timestamp with time zone", people think the data is saved
> *in* a specific time zone, whereas in reality, the opposite is true.  It
> is really more like "timestamp UTC" or you even could say "timestamp at
> UTC".  When you query this of course, then it shows you the time offset
> based on your client timezone setting.
>
> I do believe this is part of the SQL standard, but I have found that it
> creates great confusion.  I think many devs choose timestamp instead of
> timestamptz because they don't really understand that timestamptz gives
> you UTC time storage built-in.
>
> That of course means that if you have multiple servers that run in a
> different time zone, and you want to replicate that data to a
> centralized location, you can easily figure out what objective time a
> record changed, for instance, not knowing anything about what time zone
> the source system is in.
>
> So it seems to me that "timestamp with time zone" is a misnomer in a big

It actually is. It is just one timezone though, UTC.

> way, and perhaps it's worth at least clarifying the docs about this, or

https://www.postgresql.org/docs/10/static/datatype-datetime.html#DATATYPE-TIMEZONES

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

How should the above be clarified?

> even renaming the type or providing an aliased type that means the same
> thing, something like timestamputc.  Maybe I'm crazy but I would
> appreciate any feedback on this and how easily it confuses.
>
> Thanks,
> Jeremy


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Clarifying "timestamp with time zone"

Steve Crawford
It is an unfortunate historical naming.

In these conversations I tell people to just always mentally translate "timestamp with time zone" to "point in time". How it is stored internally is entirely irrelevant except to the PostgreSQL developers and can otherwise be ignored. All that matters is that PostgreSQL is capable of accepting and returning point in time data in the timezone and the format the user desires.

Cheers,
Steve

On Fri, Jun 15, 2018 at 12:58 PM Adrian Klaver <[hidden email]> wrote:
On 06/15/2018 12:24 PM, Jeremy Finzel wrote:
> Hello!
>
> We often prefer to use timestamptz or "timestamp with time zone" in our
> environment because of its actually storing "objective time" with
> respect to UTC.  But in my own work experience, I have scarcely
> encountered a case where business users, and software engineers, do not
> actually think it means the opposite.
>
> When I say "timestamp with time zone", people think the data is saved
> *in* a specific time zone, whereas in reality, the opposite is true.  It
> is really more like "timestamp UTC" or you even could say "timestamp at
> UTC".  When you query this of course, then it shows you the time offset
> based on your client timezone setting.
>
> I do believe this is part of the SQL standard, but I have found that it
> creates great confusion.  I think many devs choose timestamp instead of
> timestamptz because they don't really understand that timestamptz gives
> you UTC time storage built-in.
>
> That of course means that if you have multiple servers that run in a
> different time zone, and you want to replicate that data to a
> centralized location, you can easily figure out what objective time a
> record changed, for instance, not knowing anything about what time zone
> the source system is in.
>
> So it seems to me that "timestamp with time zone" is a misnomer in a big

It actually is. It is just one timezone though, UTC.

> way, and perhaps it's worth at least clarifying the docs about this, or

https://www.postgresql.org/docs/10/static/datatype-datetime.html#DATATYPE-TIMEZONES

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

How should the above be clarified?

> even renaming the type or providing an aliased type that means the same
> thing, something like timestamputc.  Maybe I'm crazy but I would
> appreciate any feedback on this and how easily it confuses.
>
> Thanks,
> Jeremy


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Clarifying "timestamp with time zone"

jfinzel
In reply to this post by Adrian Klaver-4


On Fri, Jun 15, 2018 at 2:57 PM, Adrian Klaver <[hidden email]> wrote:
On 06/15/2018 12:24 PM, Jeremy Finzel wrote:
Hello!

We often prefer to use timestamptz or "timestamp with time zone" in our environment because of its actually storing "objective time" with respect to UTC.  But in my own work experience, I have scarcely encountered a case where business users, and software engineers, do not actually think it means the opposite.

When I say "timestamp with time zone", people think the data is saved *in* a specific time zone, whereas in reality, the opposite is true.  It is really more like "timestamp UTC" or you even could say "timestamp at UTC".  When you query this of course, then it shows you the time offset based on your client timezone setting.

I do believe this is part of the SQL standard, but I have found that it creates great confusion.  I think many devs choose timestamp instead of timestamptz because they don't really understand that timestamptz gives you UTC time storage built-in.

That of course means that if you have multiple servers that run in a different time zone, and you want to replicate that data to a centralized location, you can easily figure out what objective time a record changed, for instance, not knowing anything about what time zone the source system is in.

So it seems to me that "timestamp with time zone" is a misnomer in a big

It actually is. It is just one timezone though, UTC.

way, and perhaps it's worth at least clarifying the docs about this, or

https://www.postgresql.org/docs/10/static/datatype-datetime.html#DATATYPE-TIMEZONES

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

How should the above be clarified?

Actually, that is a really good description.  But I would say the problem is it does not have a prominent place on the page, and that many people reading the docs will make enough assumptions about the data types before they get down to this part of the page.  What is displayed as nitty-gritty details down the page should be essential reading for any user of postgres wanting to know how to decide between timestamp and timestamptz.

There are some descriptions that tend to mislead that perhaps could be clarified.  For example, at the top of the page, timestamptz is described as "both date and time, with time zone".  Given what we all seem to acknowledge is a misleading description, I think we ought to either change this to summarize the above very helpful description, perhaps "both date and time, in UTC" or some such idea.

I like the idea of making that note that is now nested deep in the example section very prominent at the top of the page, perhaps as one of these special notes, given how critical timestamps are for nearly any relational database use.

Thoughts?

Thanks,
Jeremy

Reply | Threaded
Open this post in threaded view
|

Re: Clarifying "timestamp with time zone"

Adrian Klaver-4
On 06/18/2018 06:24 AM, Jeremy Finzel wrote:

>
>
> On Fri, Jun 15, 2018 at 2:57 PM, Adrian Klaver
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     On 06/15/2018 12:24 PM, Jeremy Finzel wrote:
>
>         Hello!
>
>         We often prefer to use timestamptz or "timestamp with time zone"
>         in our environment because of its actually storing "objective
>         time" with respect to UTC.  But in my own work experience, I
>         have scarcely encountered a case where business users, and
>         software engineers, do not actually think it means the opposite.
>
>         When I say "timestamp with time zone", people think the data is
>         saved *in* a specific time zone, whereas in reality, the
>         opposite is true.  It is really more like "timestamp UTC" or you
>         even could say "timestamp at UTC".  When you query this of
>         course, then it shows you the time offset based on your client
>         timezone setting.
>
>         I do believe this is part of the SQL standard, but I have found
>         that it creates great confusion.  I think many devs choose
>         timestamp instead of timestamptz because they don't really
>         understand that timestamptz gives you UTC time storage built-in.
>
>         That of course means that if you have multiple servers that run
>         in a different time zone, and you want to replicate that data to
>         a centralized location, you can easily figure out what objective
>         time a record changed, for instance, not knowing anything about
>         what time zone the source system is in.
>
>         So it seems to me that "timestamp with time zone" is a misnomer
>         in a big
>
>
>     It actually is. It is just one timezone though, UTC.
>
>         way, and perhaps it's worth at least clarifying the docs about
>         this, or
>
>
>     https://www.postgresql.org/docs/10/static/datatype-datetime.html#DATATYPE-TIMEZONES
>     <https://www.postgresql.org/docs/10/static/datatype-datetime.html#DATATYPE-TIMEZONES>
>
>     "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."
>
>     How should the above be clarified?
>
>
> Actually, that is a really good description.  But I would say the
> problem is it does not have a prominent place on the page, and that many
> people reading the docs will make enough assumptions about the data
> types before they get down to this part of the page.  What is displayed
> as nitty-gritty details down the page should be essential reading for
> any user of postgres wanting to know how to decide between timestamp and
> timestamptz.
>
> There are some descriptions that tend to mislead that perhaps could be
> clarified.  For example, at the top of the page, timestamptz is
> described as "both date and time, with time zone".  Given what we all
> seem to acknowledge is a misleading description, I think we ought to
> either change this to summarize the above very helpful description,
> perhaps "both date and time, in UTC" or some such idea.
>
> I like the idea of making that note that is now nested deep in the
> example section very prominent at the top of the page, perhaps as one of
> these special notes, given how critical timestamps are for nearly any
> relational database use.
>
> Thoughts?

Time/dates/timestamps are complicated and there are no end of 'if, and
and buts'. My suggestion would be a note at the top of the page that
says read through this section at least twice and then come back and do
that again. Other gotchas in the section that I have seen in posts to
this list:

"The SQL standard differentiates timestamp without time zone and
timestamp with time zone literals by the presence of a “+” or “-” symbol
and time zone offset after the time. Hence, according to the standard,

TIMESTAMP '2004-10-19 10:23:54'

is a timestamp without time zone, while

TIMESTAMP '2004-10-19 10:23:54+02'

is a timestamp with time zone. PostgreSQL never examines the content of
a literal string before determining its type, and therefore will treat
both of the above as timestamp without time zone.
To ensure that a literal is treated as timestamp with time zone, give it
the correct explicit type:

TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'

In a literal that has been determined to be timestamp without time zone,
PostgreSQL will silently ignore any time zone indication. That is, the
resulting value is derived from the date/time fields in the input value,
and is not adjusted for time zone."


"One should be wary that the POSIX-style time zone feature can lead to
silently accepting bogus input, since there is no check on the
reasonableness of the zone abbreviations. For example, SET TIMEZONE TO
FOOBAR0 will work, leaving the system effectively using a rather
peculiar abbreviation for UTC. Another issue to keep in mind is that in
POSIX time zone names, positive offsets are used for locations west of
Greenwich. Everywhere else, PostgreSQL follows the ISO-8601 convention
that positive timezone offsets are east of Greenwich."




>
> Thanks,
> Jeremy
>


--
Adrian Klaver
[hidden email]