Clarifying "timestamp with time zone"

Поиск
Список
Период
Сортировка
От Jeremy Finzel
Тема Clarifying "timestamp with time zone"
Дата
Msg-id CAMa1XUh2LTa7RFU_4ximPuu+tw8DCmW545-E7kJWJH6mnkM_BA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Clarifying "timestamp with time zone"
Re: Clarifying "timestamp with time zone"
Re: Clarifying "timestamp with time zone"
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Jeremy Finzel
Дата:
Сообщение: Re: Partitioning with range types
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Clarifying "timestamp with time zone"