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 <adrian.klaver@xxxxxxxxxxx> 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
adrian.klaver@xxxxxxxxxxx
