Re: Clarifying "timestamp with time zone" - Mailing list pgsql-general
| From | Jeremy Finzel |
|---|---|
| Subject | Re: Clarifying "timestamp with time zone" |
| Date | |
| Msg-id | CAMa1XUh1THMjoMEV5pF5ZRFV5YXhLzPKYnGmT0WSNJ+F15iEWQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Clarifying "timestamp with time zone" (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: Clarifying "timestamp with time zone"
(Adrian Klaver <adrian.klaver@aklaver.com>)
|
| List | pgsql-general |
On Fri, Jun 15, 2018 at 2:57 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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
pgsql-general by date: