Не секрет, что работа с часовыми поясами — боль, и многие разработчики объяснимо стараются ее избегать. Тем более что в каждом языке программирования / СУБД работа с часовыми поясами реализована по-разному.
Среди тех, кто работает с PostgreSQL, есть очень распространенное заблуждение про типы данных timestamp (который также именуется timestamp without time zone) и timestamptz (или timestamp with time zone). Вкратце его можно сформулировать так:
Мне не нужен тип timestamp with time zone, т.к. у меня все находится в одном часовом поясе — и сервер, и клиенты.
В статье я постараюсь объяснить, почему даже в таком довольно простом сценарии можно запросто напороться на проблемы. А в более сложных (которые на самом деле чаще встречаются на практике, чем может показаться) баги при использовании timestamp практически гарантированы.
Статья не претендует на полноту, но надеюсь, что поможет получше разобраться в этой не самой тривиальной теме :)
Про часовые пояса (time zones)
Концепция часовых поясов — на самом деле сравнительно недавнее изобретение человечества. Когда в XIX веке появились железные дороги и телеграф, люди пришли к тому, что настраивать часы в каждом городе на местное солнечное время крайне неудобно. В конечном счете планета оказалась разделена на 24 “полосы”, время в каждой из которых отстоит от ”нулевого пояса” (который обозначается как GMT или UTC) на целое количество часов. Есть, конечно, исключения вроде Индии и Ирана, но про них не в этой статье.
Казалось бы, тогда часовой пояс каждого стационарного места на Земле мог бы быть идентифицирован одним числом (смещением в часах относительно Гринвича). К сожалению, это не работает по двум причинам:
во многих странах мира (например, в Европе и США) применяется летнее время, и 2 раза в год смещение от GMT меняется;
из-за законодательных изменений города или страны могут перманентно менять часовой пояс.
Поэтому была разработана база данных tz database (https://ru.wikipedia.org/wiki/Tz_database), в которой хранится актуальное соответствие географических идентификаторов часовым поясам. Идентификаторы, например, бывают такими: America/Buenos_Aires, Europe/Paris, Europe/Moscow и т. д. Каждому идентификатору соответствует набор правил, по которым можно вычислить смещение от GMT на какую-то дату. И когда какой-то город переходит в другой часовой пояс, в базу вносятся изменения.
Как PostgreSQL работает с часовыми поясами?
В PG есть несколько типов данных, использующихся для обозначения времени: https://www.postgresql.org/docs/current/datatype-datetime.html
Здесь мы рассмотрим 2 наиболее часто встречающихся типа — timestamp и timestamptz.
В целом (по смыслу) timestamp соответствует локальному времени без учета часовых поясов, а timestamptz — времени с учетом часового пояса. Проще всего понять это, попробовав вывести текущее время с помощью функции now() без часового пояса и с ним:
public=> select now()::timestamp, now();
now | now
----------------------------+-------------------------------
2023-06-28 21:49:56.417841 | 2023-06-28 21:49:56.417841+03
(1 row)
Функция now() возвращает таймстемп в формате timestamptz, с учетом часового пояса (обратите внимание на +03 в конце).
Может сложиться впечатление, что тип timestamptz хранит “таймстемп плюс таймзону”. Но это не так — на самом деле для типа данных timestamptz хранится только время в UTC (18:49:56.417841), а при отображении вычисляется итоговое время на основании “текущей таймзоны сессии”. Обратимся к документации постгри:
All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.
Тут говорится, что текущая таймзона определяется значением системного параметра TimeZone: https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TIMEZONE
TimeZone(string)Sets the time zone for displaying and interpreting time stamps. The built-in default is
GMT, but that is typically overridden inpostgresql.conf; initdb will install a setting there corresponding to its system environment. See Section 8.5.3 for more information.
Вкратце: дефолтное значение параметра TimeZone в PG, как правило, проставляется процессом initdb (который инициализирует свежий кластер БД) равным часовому поясу хоста, на котором крутится база. Это можно проверить в файле postgresql.conf: grep '^timezone' $PGDATA/postgresql.conf. Затем каждый клиент может переопределить параметр TimeZone по своему усмотрению в рамках сессии.
Важно, что поскольку часовой пояс никогда не сохраняется вместе со значением таймстемпа, тип данных timestamptz занимает в памяти ровно столько же места (8 байт), сколько и timestamp. Поэтому аргумент про то, что использование timestamp экономит место в памяти, совершенно несостоятелен.
Хьюстон, у нас проблема
Сложности чаще всего случаются тогда, когда клиенты БД могут находиться в разных часовых поясах.
Предположим, у ООО “Рога и Копыта” есть сервер, где в postgresql.conf стоит настройка TimeZone='Europe/Moscow' (то есть UTC+3). Иными словами, в компании есть договоренность, что все “локальное” время понимается как московское. Пусть на сервере есть таблица data с двумя колонками: created timestamp и created_tz timestamptz.
Однажды к базе подключился клиент Вася из Челябинска, и у него в PG клиенте оказался проставлен TimeZone=UTC+5 (например, забыл поменять пояс на московский). Пусть в момент времени 2023-10-22 18:47:41.962110 +05:00 он решил выполнить запрос
INSERT INTO data(created, created_tz) VALUES (now(), now());Поскольку now() возвращает timestamptz, при записи в created произойдет конвертация в timestamp в текущей таймзоне сессии. Поэтому в него запишется 18:47, а в created_tz - время в UTC (13:47).
Когда Вася решит сделать SELECT и получить результат обратно, ему вернется
SELECT created, created_tz FROM data;
2023-10-22 18:47:41.962110, 2023-10-22 18:47:41.962110 +05:00Обратите внимание, что во второй колонке при отображении произошла обратная конвертация из UTC в локальное время.
А теперь предположим, что пришел злой админ Вова, у которого стоит московская таймзона (SET TimeZone=’Europe/Moscow’). И ему нужно понять, во сколько Вася добавил строчку в таблицу. Если он сделает аналогичный SELECT, то получит:
SELECT created, created_tz FROM data;
2023-10-22 18:47:41.962110, 2023-10-22 16:47:41.962110 +03:00Для created_tz UTC сконвертировалось в локальную таймзону Вовы (московскую), а вот created показывается как есть. Глядя исключительно на это поле, невозможно понять, когда же реально была произведена запись! Информация о таймзоне Васи утеряна, вообще ни разу не очевидно, что клиент находился в челябинском часовом поясе.
Хотите еще проблем — не вопрос! Зоопарк из клиентов PostgreSQL
Выше была рассмотрена ситуация, когда клиенты находятся в разных часовых поясах и из-за использования timestamp without time zone пропадает возможность определить время события.
Но многие тут резонно заметят: у меня и сервер, и все клиенты в одном часовом поясе, зачем мне думать о каких-то таймзонах?
Если бы все было так просто :) То, с чем я столкнулся при работе с разными клиентами PG, меня поразило.
Сценарий, от которого и смешно, и грустно одновременно
Воспроизвести ситуацию очень просто. Возьмем PostgreSQL, у которого таймзона сервера стоит в America/Buenos_Aires (это UTC-3):
docker run --name pgdemo -p 5432:5432 -e POSTGRES_USER=pguser -e POSTGRES_PASSWORD=pgpasswd -e TZ=America/Buenos_Aires -d postgresТут мы запустили чистый инстанс базы Postgres с названием pgdemo, а также проставили в контейнере часовой пояс с помощью переменной TZ (https://www.gnu.org/software/libc/manual/html_node/TZ-Variable.html). Поскольку в файле postgresql.conf мы ничего явно не задавали, Postgres использует часовой пояс из docker-контейнера.
Также предположим, что к базе мы подсоединяемся из московского часового пояса (UTC+3). Возьмем 3 распространенных клиента:
старый добрый psql (подключаемся через
psql postgresql://pguser@localhost:5432/postgres);встроенный клиент в IntelliJ IDEA (также используется в других продуктах Jetbrains: PyCharm, DataGrip и так далее);
DBeaver (популярный свободный десктопный клиент на основе JDBC).
Создадим таблицу (из любого клиента):
create table person (
id integer primary key,
name text not null,
created timestamp not null default now(),
created_tz timestamptz not null default now()
);Выполним из каждого клиента по запросу:
(psql)
insert into person (id, name) values (1, 'Vasya_psql');
(intellij)
insert into person (id, name) values (2, 'Kolya_intellij');
(dbeaver)
insert into person (id, name) values (3, 'Natasha_dbeaver');Таймстемпы в таком случае будут заполняться текущим временем. Пусть все клиенты физически находятся в московском часовом поясе (UTC+3). Тогда если они выполнят select * from person, то получится:
Обратите внимание - в колонку created (у которой тип timestamp) все клиенты проставили разные значения!
Почему так случилось? Функция now() возвращает текущее время в формате timestamptz. Затем происходит конвертация в timestamp. Результат определяется значением параметра TimeZone в каждой клиентской сессии и каждый клиент заполняет его по-своему. Самая боль в том, что в зависимости от реализации клиента дефолтное значение этого параметра может быть практически каким угодно:
Для psql это таймзона сервера (в нашем случае UTC-3)
Для intellij / datagrip — просто UTC (вот тут можно найти объяснение, почему так сделано: https://youtrack.jetbrains.com/issue/DBE-2996)
Для dbeaver — таймзона клиента (UTC+3), как и в целом для большинства JDBC-based клиентов: http://github.com/pgjdbc/pgjdbc/issues/576.
В результате 3 клиента, которые физически находятся на одном компьютере, заполняют поле с типом timestamp разными значениями:
Из этого следует, что даже если сервер находился бы в клиентском часовом поясе (UTC+3), как минимум intellij проигнорировал бы это и записал бы время в UTC.
Вывод: даже если у вас и сервер, и все клиенты физически находятся в одном часовом поясе, вы не застрахованы от потери данных при использовании timestamp! Запросто могут найтись клиенты, у которых TimeZone “неожиданный” и есть риск, что в поле с типом timestamp может записаться время в неправильной таймзоне. Конечно, на клиенте дефолтную таймзону можно переопределить на нужную, но это проще простого забыть сделать (да и просто неудобно подобным заниматься).
Напротив, для колонки с типом timestamptz все в порядке. Значение now() напрямую сохраняется в поле без конвертаций и проблем не возникает. Клиенты отображают значение в соответствии со своими настройками (по времени сервера, клиента или в UTC), но во всех случаях верно и с указанием таймзоны.
Как можно было бы решить проблемы?
Самый правильный подход
В подавляющем большинстве случаев стоит отказаться от использования timestamp и перейти на timestamptz.
Это позволит (а) устранить неоднозначность в интерпретации таймстемпов, которые уже есть в базе и (б) избавиться от риска, что клиент подключается с “неожиданной” таймзоной и ломает данные (как выше в случае с Вовой). При этом timestamptz не занимает больше места в памяти, чем timestamp.
Сначала имеет смысл использовать timestamptz для всех новых таблиц, затем (в рамках технического долга) мигрировать существующие таблицы с timestamp на timestamptz.
Альтернативные варианты
Бывает, что в компании исторически сложилось использовать timestamp и понимать под ним время в каком-то выделенном часовом поясе (например, в UTC либо в MSK). Также код, который работает с БД, может иметь сложности с поддержкой timestamptz.
Какие есть альтернативы, если быстро перейти на timestamptz затруднительно?
Клиент может генерировать таймстемп сам и передавать его в команде INSERT в явном виде (с часами, минутами и секундами), а не использовать встроенную функции PG now() (и аналогичные, которые возвращают timestamptz). Тогда не будет производиться неявная конвертация из timestamptz в timestamp, поэтому таймзона клиента не будет влиять на результат. Если таймстемп генерируется вручную — то можно пользоваться функцией make_timestamp:
INSERT INTO data(created) VALUES (make_timestamp(2023, 10, 22, 9, 30, 0));Также локальный таймстемп может генерироваться в коде приложения явно (например, черезLocalDateTime.now()в Java).
Минус подхода - ответственность за корректность добавляемого значения возлагается на клиента, что увеличивает вероятность ошибок. Также нужно дополнительно следить, чтобы все клиенты, которые пишут в базу, имели корректные настройки таймзоны.Если необходимо вычислять текущее время на стороне БД, можно выполнять конвертацию в timestamp явно, используя AT TIME ZONE:
INSERT INTO data(created) VALUES (now() AT TIME ZONE 'Europe/Moscow');Минус подхода — это требует действий в каждом скрипте, и каждый скриптописатель сам должен следить за тем, какую таймзону указывает (и что она соответствует его актуальной). Забыть указать AT TIME ZONE или указать неправильную таймзону — проще простого. А поскольку после вставки в базу информация о таймзоне теряется, раскопать потом что-то может быть невозможно.
Исключение: когда все же стоит использовать тип данных timestamp (without time zone)?
На практике из любого правила бывают исключения. С timestamp это тоже так — есть сценарий, когда других вариантов по сути нет: когда вам нужно задать какое-то время в неопределенном часовом поясе. Например, время для напоминания в будущем, для будильника или другого действия по расписанию. Чаще всего вам важно, чтобы будильник сработал, условно, в 9 утра по местному времени в какой-то конкретный день, при этом не важно, какой у вас при этом будет часовой пояс. Вы вообще можете уехать в другое место или часовой пояс в вашем городе может законодательно поменяться. В таких случаях наиболее логично применять именно timestamp [without time zone].
Подведем итоги: почему timestamptz предпочтительнее?
Тип данных timestamptz занимает столько же места в памяти, сколько и timestamp, при этом информации фактически содержит больше
При использовании now(), current_timestamp, localtimestamp и т.п. результат того, что запишется в базу, не зависит от того, в каком часовом поясе клиент
Даже если сервер и все клиенты в одном часовом поясе — нет проблем с разным дефолтным значением параметра TimeZone в разных PG-клиентах (например, из-за дефолтного UTC в intellij)
Исключение по сути одно — время с неизвестным / неопределенным часовым поясом (например, в будущем). В таком случае timestamp действительно является оптимальным выбором.
Ссылки для дальнейшего изучения:
https://www.iso.org/iso-8601-date-and-time-format.html
https://www.postgresql.org/docs/current/datatype-datetime.html
https://www.postgresql.org/docs/current/functions-datetime.html
https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29
https://phili.pe/posts/timestamps-and-time-zones-in-postgresql/
Комментарии (108)
sshikov
09.11.2023 16:00+3All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.
Ну это еще и означает, что скорее всего оно все не совместимо скажем с тем, как сделано в Оракле. Потому что документация Оракла утверждает, что
The
TIMESTAMP WITH TIME ZONEdata type stores both the time stamp and time zone data.Ну и кстати насчет отображение - это некоторое упрощение. Есть же to_char, и там можно выбрать много чего (но, нельзя например выбрать формат отображения Europe/Moscow, и из вашего объяснения становится ясно, почему так - потому что оно не хранится). Оракл это умеет.
propell-ant
09.11.2023 16:00+10Хорошая статья, но пример применения "время для напоминания в будущем, для будильника или другого действия по расписанию" лучше выпилить.
Время timestamp, которое из базы получит клиент - это не время относительно начала сферических суток в вакууме, это вполне конкретный момент времени на определенную дату.Как только клиент начнет вычисления с этим значением, он пойдет по тропинке, устланной граблями - он же в другом часовом поясе, а значит может оказаться и в стране, где переходят на зимнее/летнее время.
Короче, хранение времени нужно спокойно и вдумчиво проектировать, без должного внимания с любым форматом можно огрести проблемы.
dmserebr Автор
09.11.2023 16:00-1Время timestamp, которое из базы получит клиент - это не время относительно начала сферических суток в вакууме, это вполне конкретный момент времени на определенную дату
По идее все же нет. timestamp означает "просто время, без даты, часового пояса или чего-то другого". Понимать его как конкретный момент времени или нет — это исключительно договоренность тех, кто работает с типом, это не свойство самого типа.
Ну и хорошие альтернативы в случае конкретно PG трудно подобрать.
nin-jin
09.11.2023 16:00+3Правильное решение в таких случаях - iso8601, а не полагаться на то, что "число секунд от начала эпохи" вдруг даст ровно то, что ввёл пользователь. В вашем примере это будет строка
2023-10-22T09:30без секунд и таймзон.
propell-ant
09.11.2023 16:00+2timestamp означает "просто время, без даты, часового пояса или чего-то другого"
Надеюсь, что это у вас опечатка насчет "без даты". В PG пишут, что там и дата и время с разрешением в 1 микросекунду.
Ну и в статье вы сами при сравнении показываете наличие дат:_ now | now _
----------------------------+-------------------------------
2023-06-28 21:49:56.417841 | 2023-06-28 21:49:56.417841+03
sshikov
09.11.2023 16:00+4Строго говоря, расписание может быть как привязано к зоне, так и нет. Сервер как правило никуда не переезжает, поэтому его события - они в локальной зоне, которую можно считать константой (хотя тоже... кто знает) либо в UTC.
Если же это расписание событий клиента - то он просто по определению может быть в любой зоне, причем в течение дня в разных. Так что его расписание должно включать зону.
без должного внимания с любым форматом можно огрести проблемы.
146%
Finesse
09.11.2023 16:00+3Представьте, у вас на ноутбуке настроено автообновление виндоуса (не кидаетесь помидорами, просто представьте) на 4 утра. Вы летите в командировку из Москвы во Владивосток, там время +7 часов. В 11:00 по местному времени в вас важная презентация или что-то ещё, что требует ноутбук. Если время обновления хранится в timestamptz, то виндоус начнёт обновляться в самый неподходящий момент. Вы можете спросить «причём здесь сервер». Дело том, что настройка времени обновления может храниться на сервере.
Я не говорю, что надо использовать timestamp вместо timestamptz. Я хочу показать, что бывают кейсы, когда timestamp имеет смысл.
sshikov
09.11.2023 16:00Не увидел с ходу прямой связи. Я не говорил что тут нет кучи разложенных граблей - наоборот, они тут на каждом углу лежат. Я говорил, что расписание может логически быть привязано к какой-то зоне по умолчанию (локальной сервера или UTC), или же к зоне клиента (который мобилен), или к конкретной именованной зоне типа Владивостока. Все варианты, использованные не по назначению, скорее всего приведут к проблемам. То что вы описываете - ну наверное одна из таких потенциальных проблем.
Я хочу показать, что бывают кейсы, когда timestamp имеет смысл.
Так я вообще этого ни разу не отрицал.
Akina
09.11.2023 16:00-2В результате 3 клиента, которые физически находятся на одном компьютере, заполняют поле с типом timestamp разными значениями ... Напротив, для колонки с типом timestamptz все в порядке.
Есть подвох. Дело в том, что нигде в БД не хранятся сведения о том, какая собственно зона на сервере в момент вставки записи. То есть поле типа timestampz даёт правильное относительное расположение/смещение меток друг от друга, но никоим образом не указывает на абсолютный момент времени для хранящегося значения. Нет, пока всё остаётся в рамках одного сервера, и его не перестраивают, всё в порядке. А если надо перенастроить зону? А если надо передать дамп для развёртывания в другой зоне?
Так что как по мне, то хранение timestamp плюс зоны времени клиента в дополнительном поле ещё надёжнее. Несмотря на бОльшее потребление пространства (аж цельный байт на запись, да ещё и с учётом страничности хранения) и повышенную сложность обработки.
dmserebr Автор
09.11.2023 16:00+10По-моему, вы что-то путаете. Если вставлять запись с типом timestampz, это означает буквально следующее "в момент вставки было столько-то по UTC". Тут никак не задействованы относительные смещения, просто хранятся моменты по UTC плюс признак "покажи мне эту временную метку по той таймзоне, которая тебе нужна".
Если сервер переезжает в другой часовой пояс, моменты по UTC никуда же не переезжают :) Меняется только дефолтный TimeZone. Условно, раньше поле было 12:10:05 +02:00, стало 14:10:05 +04:00, но это же фактически один и тот же момент времени.
Akina
09.11.2023 16:00-4Значение штампа времени на самом деле имеет два значения - мировое и локальное. Иногда одни и те же данные в зависимости от текущей их обработки должны считаться либо как абсолютное, либо как местное время. Вот смотрим мы в таблицу, и пытаемся понять - а какое своё локальное время записал пользователь? пользователей много, зоны разные, да они ещё и в командировки мотаются... а этой информации в базе опять же нету.
Ну не запихивается два значения сразу в одно поле. Так что если мы имеем дело с необходимостью такой двойной интерпретации, нас ни timestamp, ни timestampz отдельно не устроят. Только с довеском - информацией о текущей клиентской зоне.
inkelyad
09.11.2023 16:00+6Вот смотрим мы в таблицу, и пытаемся понять - а какое своё локальное время записал пользователь?
Мне кажется, что вот это - нельзя ни самому попринимать как что-то близкое к термину timestamp, ни другим позволять. И, соответственно, стоит пытаться не использовать хоть что-то близкое к этому термину 'timestamp', даже если это тип поля. Ибо проблем на свою и чужую голову соберешь.
Потому что timestamp вроде бы все более-менее универсально воспринимают как 'если одинаковые, то события произошли одновременно'.
А 'свое локальное время' благодаря тому, что часы в некоторых случаях 'переводятся назад' -- могут и повториться с достаточно большим промежутком между ними.
kenoma
09.11.2023 16:00+10Да просто как стандарт надо хранить время в UTC и переводить по мере надобности на стороне клиента.
uhf
09.11.2023 16:00+9Как-то усложнено все в PostgreSQL =) Разделение на типы timestamp и timestamptz сбивает с толку, можно подумать что они чем-то отличаются в их содержании, хотя получается разница лишь в том, что timestamptz автоматически конвертируется в UTC из часового пояса соединения и обратно, а timestamp нет.
В MySQL тип timestamp сразу имеет поведение как timestamptz в PostgreSQL, и я считаю что это правильно. Если не нужна конвертация - нужно следить за таймзоной коннекта, ну можно еще хранить таймстемп целым или даже строкой.
BearOff
09.11.2023 16:00+2В MySQL тип timestamp сразу имеет поведение как timestamptz в PostgreSQL
В MySQL есть DATETIME, который соответствует timestamp из PostgreSQL.
uhf
09.11.2023 16:00Я бы не сказал, что соответствует, это разные типы - у них разный формат хранения данных, разные диапазоны допустимых значений. Общее только то, что часовой пояс автоматически не преобразуется в UTC и обратно.
BearOff
09.11.2023 16:00Да, но в контексте статьи и обсуждения именно хранение в UTC/хранение как есть - это ключевое отличие типов.
Fedorkov
09.11.2023 16:00+3Это особенность стандарта SQL, которому постгрес следует с большей строгостью, чем MySQL.
В силу возраста стандарт SQL тянет за собой много непродуманных мелочей, а работы с датами и временем это касается в особой степени.
slonopotamus
09.11.2023 16:00+2Глядя исключительно на это поле, невозможно понять, когда же реально была произведена запись!
С учётом того что в вашем случае Вася из Челябинска может передавать какие угодно значения в INSERT, вам никакие типы таймстемпов не помогут понять когда же была вставлена запись.
dmserebr Автор
09.11.2023 16:00-1Там речь про конкретную ситуацию — когда Вася хочет вставить просто текущее время и вызывает функцию
now()— но получается не то, что ему хотелось.
Вы же как будто говорите про намеренную порчу данных в таблице, а это совсем другой случай.
iamkisly
09.11.2023 16:00+1Я думаю, что Вася должен обладать компетенциями, если пишет сырые запросы в базу..
ptr128
09.11.2023 16:00Это уже намеренные действия. А речь идёт о том, что вставляя записи по одной в десять минут, после записи в 2:55 легко может прилететь запись в 2:05, в связи переходом на зимнее время.
TrueRomanus
09.11.2023 16:00+15Интересное исследование и интересные выводы но спешу Вас отговорить от поспешных переделок всех полей c timestamp на timestampz.
Тип timestampz предназначен для случаев когда Вам надо видеть (именно видеть!) дату+время в какой-то часовой зоне (обычно своей но это не важно).
И это как раз узкий кейс в то время как тип timestamp предназначен для всех остальных случаев когда нам нужна просто гипотетическая дата+время. Если Вам требуется точная система которая обеспечивает гарантии на что бы то ни было связанное с временными зонами то ни timestamp ни timestampz Вам это не обеспечат. По Вашей логике клиент не несет ответственности за ту таймзону которую он настроил в сессии, но это не корректное утверждение. Каждый клиент ответственен за то какую таймзону он настроил. Условный дядя не устанавливает же время на Ваших часах, да Вы можете подключить службу по синхронизации времени но опять же это будет Ваше осознанное решение. К тому же замена типов порождает уже другую проблему, как я и сказал выше тип timestampz предназначен чтобы видеть дату+время в каком-то конкретном часовом поясе. Дак вот предположим ситуацию: есть 5 сотрудников работающих с базой каждый работает в своей часовой зоне (или как мы выяснили из статьи даже просто в разных клиентах). Один из них тестер который нашел багу в двух полях с датами в мобильном приложении и снял скриншот в зоне -5 с ошибкой и зарепортил баг. Разработчик в зоне +5 получив баг решил воспроизвести проблему у себя и попытался найти записи с датами как на скриншоте но он их не нашел а чтобы найти их ему надо было бы выполнить пересчет дат из -5 в utc и написать field = utc_date но результат запроса он увидит в +5. Т.е. все операции такие как больше, меньше равно и т.п выполняются не по той дате которую ты видишь а по utc что требует от пользователя выполнения операций в уме. Я уж не говорю сколько будет матов сложены администратором который в зоне -10 когда ему потребуется выбрать всех пользователей которые что-то когда то сделали и выгрузить в отчет. Другая проблема приведение к какому-то часовому поясу может давать другую дату что может еще больше повысить когнитивный диссонанс. Что подводит нас к одной простой мысли, установка таймзоны это обязанность клиента а если это так то Ваш главный аргумент в пользу незамедлительного уничтожения полей с типом timestamp уже не выглядит таким уж весомым.
Rampages
09.11.2023 16:00... попытался найти записи с датами как на скриншоте но он их не нашел а чтобы найти их ему надо было бы выполнить пересчет дат из -5 в utc ...
А почему он ищет записи там, если изначально знает что репорт может быть в разных таймзонах? ну то есть камон время на экране != время на сервере/в базе/логах/журналах.
Вообще в базе может что угодно быть, главное чтобы на клиента правильно отдавалась информация о времени, т.е. вот я пишу комментарий по времени UTC+6, вы его читаете допустим по времени UTC+3, и он должен у вас отображаться по времени UTC+3 и если я поменяю свой часовой пояс на UTC+10 и напишу еще пару комментариев, то это не должно влиять на ваше время и аналогично если вы поменяете свой часовой пояс.
offline268
09.11.2023 16:00камон время на экране != время на сервере/в базе/логах/журналах.
Ты это безопаснику объясни )))
Идет значит созвон, что-то там обсуждаем, я шарю экран, говорю, вот мол логи, вот такие-то события. А безопасник мне - "Чо ты голову мне морочишь, у тебя в логах 18:25, а мы действие в 16:25 делали..."
А я всего то логи в кибане смотрю из еката, а они там в москвах сидят и не верят)))inkelyad
09.11.2023 16:00А безопасник мне - "Чо ты голову мне морочишь, у тебя в логах 18:25, а мы действие в 16:25 делали..."
Он, в некотором смысле, прав. Если в интерфейсе сразу не понятно, в какой именно таймзоне время показано, но этот такой вариант интерфейса не стоило бы для аудита безопасности использовать. Т.е. не стоит показывать безопаснику как самостоятельно, так и когда он туда сам заходит. А то кто-то что-то рано или поздно не так поймет.
TrueRomanus
09.11.2023 16:00А почему он ищет записи там, если изначально знает что репорт может быть в разных таймзонах? ну то есть камон время на экране != время на сервере/в базе/логах/журналах.
Тут разница не в том где он ищет а в том что время которое он видит у себя на экране (в своей таймзоне) не равно времени которое на самом деле записано в базе (в UTC). И когда он хочет поискать по времени, например колонка с датой < конкретная дате и время, ему надо из времени которое он видит у себя на экране в уме перевести в UTC либо явно указать перевод в UTC используя сессию или прописать в запросе иначе он либо не найдет того что ищет либо найдет что-то другое.
BugM
09.11.2023 16:00+5Как же вы все усложнили. Да и авторы постгреса тоже.
Таймстамп это прекрасная штука. Она однозначно задает любой момент времени на временной шкале. Нет никаких таймзон, поясов, зимнего времени и прочего. Есть просто точка на шкале абстрактного времени.
И эту точку перед показом клиенту можно превратить во что угодно и отобразить как это нужно клиенту. Это превращение абсолютно однозначно и требует только знания чего клиент хочет. От ваших данных алгоритм превращения не зависит никак.
И в вашей базе всегда будет порядок. Вы всегда легко можете сказать какое событие наступило раньше. Без всяких сложных операций, просто сравнив две циферки.
А определить в какую дату у клиента произошло то или иное событие всегда нетривиально. Таймзона клиента мало того может быть любой, так она еще и меняться может. Сделайте какой-то нормальный инвариант на эту тему и соблюдайте его везде. Например так: "клиент указывает таймзону которая ему нужна в настройках и все события по дням раскладываются в соответствии с этой таймзоной. менять настройку можно когда угодно. на старые события не влияет, новые поедут по новой таймзоне"
Finesse
09.11.2023 16:00+2Есть просто точка на шкале абстрактного времени
Проблемы возникают при преобразовании времени из абстрактной шкалы в шкалу реального времени. Статья про это как раз.
Timestamptz хранит время на шкале реального времени. Так как такие таймстэмпы всегда содержат часовой пояс при преобразовании в строку, вы точно знаете, какой часовой пояс имеется ввиду, где бы вы не находились и каким клиентом не пользовались. Язык программирования сервера тоже учитывает этот часовой пояс.
Вы всегда легко можете сказать какое событие наступило раньше. Без всяких сложных операций, просто сравнив две циферки.
С timestamptz тоже самое. Под капотом там только unix-метка времени. Таймзона подставляется только в момент преобразовании к строке, чтобы устранить неоднозначности, то есть не вспоминать каждый раз «в каком часовом поясе мы храним время». Таймзона одинаковая в пределах сессии, поэтому глазами сравнивать легко. Таймзона, которую вы видите а БД, не зависит от таймзоны клиента, который сделал запись в БД.
BugM
09.11.2023 16:00-2Проблемы возникают при преобразовании времени из абстрактной шкалы в шкалу реального времени
Реальное время это как раз точка на абсолютной шкале. Остальное все придумано где-то живущими людьми для удобства отображения.
С timestamptz тоже самое. Под капотом там только unix-метка времени. Таймзона подставляется только в момент преобразовании к строке, чтобы устранить неоднозначности, то есть не вспоминать каждый раз «в каком часовом поясе мы храним время». Таймзона одинаковая в пределах сессии, поэтому глазами сравнивать легко. Таймзона, которую вы видите а БД, не зависит от таймзоны клиента, который сделал запись в БД.
Это же ужасно. В БД должна лежать и отображаться при простом селекте конкретная циферка. Инт проще говоря. По этой циферке ни один разработчик или аналитик не сделает неверного предположения или неверных выводов. Там просто негде ошибаться. Инт он и есть инт. 1970 год в наших сердцах.
Информацию о какой тайм зоне думал/хотел/находился/мечтал пользователь может быть стоит заботливо сохранить рядом. А может и нет. Зависит от конкретной реализации и бизнес логики вашего приложения. Абсолютная точка на временной шкале от этого точно не зависит.
А вот когда надо красиво показать в интерфейсе этот инт легко и однозначно преобразуется в нужные пользователю дату-время.
Остается только проблема как получить этот инт из данных пользователя. Тут как раз стоит три раза подумать, четыре посовещаться и принять одно решение. И с этого момента у вас будет точное правило получения таймстемпа из данных пользователя. Во всем вашем приложение это правило должно быть одно.
А дальше дело техники. Правило получения таймстемпа у вас есть. Правила отображения делайте как удобно пользователю в любом конкретном случае. Хоть везде по разному. Это вопрос к тем кто интерфейсы проектирует. В АПИ отдавайте таймстемп. Прямо интом и отдавайте. Клиенты АПИ вам только спасибо за это скажут.
Finesse
09.11.2023 16:00+2Это же ужасно. В БД должна лежать и отображаться при простом селекте конкретная циферка. Инт проще говоря.
В АПИ отдавайте таймстемп. Прямо интом и отдавайте. Клиенты АПИ вам только спасибо за это скажут.
Я работал с приложениями, где дата в базе хранится в интах. Во-первых, с этим неудобно работать, когда смотришь базу вручную, потому что для преобразования в читаемую дату приходится использовать сторонние инструменты или писать кастомный SQL-запрос. Во-вторых, циферка может быть воспринята по разному: секунды в UTC, секунды в часовом поясе компании, секунды по Москве, миллисекунды (как в JS) и т.п. То есть те же проблемы что у timestamp плюс дополнительные. Timestamptz однозначно указывает на момент времени в этой вселенной.
Информацию о какой тайм зоне думал/хотел/находился/мечтал пользователь может быть стоит заботливо сохранить рядом. А может и нет. Зависит от конкретной реализации и бизнес логики вашего приложения. Абсолютная точка на временной шкале от этого точно не зависит.
Я о том же говорю. Timestamptz не хранит часовой пояс. Он хранить только unix-таймстэмп по UTC. Когда читаешь его, БД для удобства приводит хранимый int в строку даты-времени и добавляет в конце часовой пояс, чтобы человек или программа точно знали, в каком поясе указано это время, и безошибочно могли преобразовать к нужному поясу.
Тут как раз стоит три раза подумать, четыре посовещаться и принять одно решение. И с этого момента у вас будет точное правило получения таймстемпа из данных пользователя. Во всем вашем приложение это правило должно быть одно.
А потом придёт новый разработчик (или старый забудет), что-то напутает и привет баги. Даты вида
2023-11-10T14:05:14+10:00(это стандарт ISO-8601) парсятся стандартными библиотеками языков программирования и базами данных так же непринуждённо как unix-таймстэмпы. То есть не нужно договариваться ни о каких правилах, и не важно, какой часовой пояс выставлен в БД и сервере. Рекомендую попробовать использовать ISO-8601 в апи. Всё, что нужно не забыть, это вызватьsetTimezoneна дате перед демонстрацией пользователю, если ЯП не делает это автоматически (JS делает).sshikov
09.11.2023 16:00+1Timestamptz не хранит часовой пояс.
Не забывайте, что это только про постгрес. В оракле все чуть иначе. Что там в стандарте, например, и кто к нему ближе - я лично не знаю.
BugM
09.11.2023 16:00-2Чиселка и инт это для красивого слова. Типизация конечно нужна и храним таймстемп. Это сразу снимает вопросы про размерность и прочее.
У нас с вами разные определения клиента. У меня это человек или внешняя система потребляющие данные. У вас это компьютер сделавший запрос в БД.
Что там за таймзона на компьютере сделавшем запрос не знает обычно никто. Компьютеры бывают разные от Jupiter тетрадки аналитиков запущенной неизвестно где до какой-то системы собирающий логи и что-то мониторящей. И в конце концов компы разработчиков которые сидят в произвольных таймзонах. И они все должны постоянно думать о том почему им показалось то или иное время. И конечно же ошибаться.
При этом какую тайм зону клиент из моего определения хочет увидеть можно определить однозначно. От настроек в вашей приложеньке и до параметров условного гет запроса человека к вашему серверу. А вот все кто пошел напрямую в базу хотят видеть одно и тоже. Чтобы потом с огромным WFT не пересылать друг другу скриншоты и не выяснять почему оно не совпадает.
Распарить что-то проблем нет ни в каком случае. Я именно про хранение.
EdwardBatraev
09.11.2023 16:00+5К сожалению 1970 год в наши сердца пришел в разное время в течении суток...
BearOff
09.11.2023 16:00-1По этой циферке ни один разработчик или аналитик не сделает неверного предположения или неверных выводов. Там просто негде ошибаться.
Например, надо вам создавать рекуррентные задачи - каждый день в 10-00 у вас митинг.
Вы храните дату как UTC timestamp.
Вы создаёте первую задачу, когда у вас действует DST, и дальше сервер за вас создаёт задачи, добавляя ровно сутки к дате предыдущей задачи.Когда DST у вас перестаёт действовать, вы видите, что ваш митинг теперь не в 10-00, а в 9-00.
При этом для вашего колеги в таймзоне, для которой DST не применяется, митинг, конечно, будет по его старому локальному времени.
Для таких случаев лучше хранить дату и время "как есть", без конверсий в UTC, но хранить также и "базовую" таймзону (того, для кого митинг всегда будет в 10-00) - чтобы из неё конвертировать в зоны других юзеров с учётом DST.
BugM
09.11.2023 16:00-1Я не понял. Как какое-то изменение чего-то может повлиять на прибавление 86400 секунд? Как прибавляли, так и будем прибавлять. И все будет работать самым ожидаемым способом и не вызывать удивления пользователей.
А что показывать пользователю зависит от желания пользователя. Показывать в интерфейсе время встречи по локальному времени пользователя вроде логично. И стоит не забыть таймзону которую мы считаем для пользователя правильной показать где-то и дать возможность изменить. А то всякое бывает.
Хранить как есть стоит только если мы не понимаем что там. Авиабилеты с разными таймзонами вылета и прилета это хороший пример когда мы не понимаем как правильно. И тогда я бы подумал про хранение в строке. Просто чтобы никто не решил что наша система обладает знанием о верных таймзонах.
inkelyad
09.11.2023 16:00+3Я не понял. Как какое-то изменение чего-то может повлиять на прибавление 86400 секунд?
Та, что организатор митинга хочет следующий не в 9:00 (которые получатся, если к сегодняшним 10:00 добавить 86400 секунд и потом пересчитать в переведенное время), а все так же в 10:00 его локального времени.
BugM
09.11.2023 16:00Это явное изменение встречи. Во встречах типично более одного человека. И эти люди часто в разных изменяющихся таймзонах.
Встреча которая сама ездит по времени вслед за организатором это фейл. У людей брони переговорок, планы какие-то. А тут все внезапно сдвинулось на 4 часа.
BearOff
09.11.2023 16:00Судя по тому, что вы упомянули 4 часа, вы не совсем поняли контекст, так как DST это никогда не 4 часа.
Именно явное изменение встречи произойдёт, если вы НЕ учтёте DST, и продолжите конвертировать UTC время в локальную таймзону.
Пример с цифрами:
вы создаёте рекуррентный митинг, каждый день в 10 часов, в таймзоне UTC+1 (Berlin), 26 октября. Так как в это время действует DST, и время в Берлине UTC+2, в базе вы получите26 октября, 8:00 UTC. Дальше сервер будет добавлять 86400 чтобы запланировать следующие митинги.
26 окт, 8:00 UTC => 26 окт, 10:00 Berlin
27 окт, 8:00 UTC => 26 окт, 10:00 Berlin
28 окт, 8:00 UTC => 26 окт, 10:00 Berlin
-- Berlin переходит на зимнее время, теперь там UTC+1
29 окт, 8:00 UTC => 26 окт, 9:00 BerlinЧто это, как не изменение времени встречи? :)
Если участники встречи находятся в разных таймзонах с разным DST, вам необходимо определить, для какой из таймзон митинг будет всегда 10:00, а для кого - меняться.
Там, на самом деле, есть и ещё нюансы - например, DST может меняться даже для одной таймзоны (решением правительств).
BugM
09.11.2023 16:00Вы опять усложнили на пустом месте. И заодно запутали своих пользователей.
Пользователь хочет сделать совещание на 10 человек. Пользователь находится в ТЗ Берлина. Пользователь задает время совещания на 10-00 по Берлину. Это время завтра переводится в таймстемп и фиксируется навсегда. И задается что совещание повторяется каждые 86400 секунд.
После этого всем 9 другим участникам совещания приходит уведомление что их позвали на ежедневное совещание во столько какая таймзона у них выставлена. Серверу вообще все равно какие у них таймзоны. Он просто их использует чтобы показать красивое и удобное текущему пользователю время совещания.
Все. После этого время совещания фиксированно навсегда. Какой участник куда уехал всем все равно. И это опять разумно. Почему других участников совещания это должно волновать? При этом уехавшим куда-то в интерфейсе будет показываться его местное время. Это удобно.
Если совещание надо перенести на другое время, то это активное действие пользователя. Отредактировать и изменить время. Всем придет уведомление что тут поменяли время совещания. И что это сделал такой то человек.
BearOff
09.11.2023 16:00+1Ваш пример про "переехал" совершенно нерелевантен для моего комментария. Да, если кто-то меняет сам свою таймзону - вы всё правильно говорите, остальных это не касается.
В моём примере никто никуда не переезжает и свою таймзону не меняет, а время встречи, тем не менее, сдвигается (если вы его храните в UTC и конвертируете в локальное).
Простите, но мне кажется, вы то ли не знаете, что такое DST (летнее время), то ли невнимательно прочитали мой пример.
BearOff
09.11.2023 16:00Пользователь задает время совещания на 10-00 по Берлину. Это время завтра переводится в таймстемп и фиксируется навсегда. И задается что совещание повторяется каждые 86400 секунд.
Поясню - обратите внимание на строчку "-- Berlin переходит на зимнее время, теперь там UTC+1".
UTC не имеет DST, а Берлин - имеет. И между 28 окт, 10:00 Berlin и 29 окт, 10:00 Berlin не 86400 секунд, а на 3600 секунд меньше.
(В комментарии выше я забыл проинкрементировать даты в правой колонке, для Берлина).
BugM
09.11.2023 16:00-1Стоп. А почему остальные 9 человек не из Берлина должны двигать свой график на час? Пусть берлинец сменит свое время совещания, у него же таймзона сменилась.
Нынче ситуация когда все в одной ТЗ и все вместе переходят на зимнее время редкость. Можно обработать как корнер кейс
BearOff
09.11.2023 16:00В моём примере рассматривается упрощённый случай: все 10 человек в Берлине )
Но если вы будете просто добавлять 86400 к UTC дате, время всё равно сдвинется с 10:00 до 9:00 для них, когда берлинская таймзона перейдёт на зимнее время.
Если же у вас распределённая команда, то в любом случае какая-то часть команды должна быть базовой (для которой время круглогодично будет 10:00), а для всей остальной команды - будет плавать.
Но опять-таки, вам нужно для этого не только хранить время (как есть, не в UTC), но и эту базовую таймзону.
И конверсия будет проводиться для каждого конкретного времени из базовой таймзоны во вторичные.
BugM
09.11.2023 16:00На практике такой календарь сделанный из расчета что все в одной таймзоне и все вместе переходят на зимнее время в 2023 не очень.
В любом другом случае такая логика будет не очень. У людей внезапно совещание сдвинется на час. Это вызывает много непонимания и WTF у людей. Календарь не должен так работать. Он должен быть максимально предсказуемым.
Схему слежения за любой таймзоной или любым локальным временем стоит делать отдельной фичей поверх календаря в таймстемпах. Пару раз в год добавить больше или меньше не сложно. В интерфейсе одна галочка.
BearOff
09.11.2023 16:00На практике такой календарь сделанный из расчета что все в одной таймзоне и все вместе переходят на зимнее время в 2023 не очень.
Послушайте, это не имеет значения, все или не все переходят на зимнее время. Имеет значение, что зимнее и летнее время существует, но не для UTC. Вы просто не можете хранить рекуррентное время в UTС и получать одно и то же время для всех весь год.
"Все в одной таймзоне" - это упрощённый пример, чтобы вам пояснить, в чём проблема.
В любом другом случае такая логика будет не очень.
Да во всех случаях логика хранения в UTC рекуррентного времени не очень. Ни в каком она не "очень", кроме случаев, когда вся команда находится в таймзоне без DST. Но мы же не можем реализовывать только этот случай, правда?
У людей внезапно совещание сдвинется на час. Это вызывает много непонимания и WTF у людей. Календарь не должен так работать. Он должен быть максимально предсказуемым.
Всё верно. И именно это произойдёт, если следвать вашему совету всегда и везде хранить время в UTC и конвертировать его по надобности в локальное.
Схему слежения за любой таймзоной или любым локальным временем стоит делать отдельной фичей поверх календаря в таймстемпах. Пару раз в год добавить больше или меньше не сложно. В интерфейсе одна галочка.
Позвольте мне не пояснять детали, но наслаивать рукописную логику учёта DST для всех поддерживаемых таймзон - это не то чтобы не сложно, это прямо-таки на порядок, а скорее на два сложнее, чем просто хранить дату+время (10:00) с базовой таймзоной, и конвертить из базовой таймзоны в любую другую.
Возможно, вы не верите мне, но мне пришлось довольно тщательно изучить этот вопрос, и в подтверждение вот вам ссылки на StackOverflow, с которых я начинал (а их море, так как это практически такая же вечная проблема, как инвалидация кэша):
Пожалуйста, обратите внимание на пункт
When scheduling future events, usually local time is preferred instead of UTC, as it is common for the offset to change. See answer, and blog post.
BugM
09.11.2023 16:00Потому что таймзоны у разных людей разные. Зимнее время тоже разное или его нет вообще. Делать фичу по дефолту с расчетом что у всех участников встречи все одинаково откровенно странно. Таки 2023 год на дворе. Время распределенных команд и удаленки.
Люди не в одной таймзоне. Они в разных. Да еще и перемещаются между таймзонами. Это давно уже дефолт.
Конвертация и перенос по локальному времени одного из участников как раз вызывает много WTF у всех. Мол чего это 23 ноября (условно) у нас встреча на час сместилась? При этом организатор тоже может не понимать, он еще летом другую страну перебрался.
При всем этом фича слежения и авто переноса за указанной таймзоной хорошая. Она может пригодиться пользователям. Но дефолтом это делать точно не стоит.
Вы точно писали массовые приложения с датой-временем? Я не придумываю все это. Это реальность современной разработки.
BearOff
09.11.2023 16:00+1Да, я точно писал массовые приложения с датой-временем (успешно).
Простите, на этом я завершаю дискуссию, так как вы просто игнорируете то, что я пишу, и в то же время пытаетесь приписать мне и тут же опровергнуть то, чего я не говорил )
Плюс в карму за возможность подробно объяснить, что не так с "только UTC" :)
BearOff
09.11.2023 16:00(В комментарии я забыл проинкрементировать даты в правой колонке, для Берлина - они такие же, как для UTC).
nightlord189
09.11.2023 16:00+5Для будильника лучше подошел бы просто time - он как раз время и хранит, без даты.
nin-jin
09.11.2023 16:00-10Вчера мне надо было встать в 7 часов, чтобы сдать кровь, и я поставил будильник. Сегодня он меня снова разбудил. Большое спасибо вам за этот дельный совет.
nightlord189
09.11.2023 16:00+9Пожалуйста, меня каждый день такие будильники на работу будят)
netch80
09.11.2023 16:00+3Телефон без одноразового будильника - диверсия сам по себе, независимо от TZ.
Borjomy
09.11.2023 16:00Чисто математически из одного числа невозможно однозначно получить два. У нас, например, локальные базы клиентов находятся в разных городах страны, со своими часовыми поясами. Иногда их записи о событиях надо просматривать в головном офисе. В результате получается, что, чтобы выяснить местное время, когда произошло событие, надо знать его временную зону. Потому что архив базы, покидая пределы региона, эту информацию теряет.
Поэтому лучше не жадничать и хранить временную зону отдельно. Это позволяет при необходимости получить и локальное время источника события и однозначно пересчитать его в локальное время в другой местности.
DrGluck07
09.11.2023 16:00А если просто добавить опцию для клиента "отображать время в часовой зоне ЧЧ"?
iamkisly
09.11.2023 16:00Достаточно хранить часовой пояс клиента/офиса/региона. На фоне обьема остальных данных это копейки.
mentin
09.11.2023 16:00+3timestamp означает "просто время, без даты, часового пояса или чего-то другого"
Иногда именно это и надо, а не "физическое" время. Скажем, вы арендуете машину в Екатеринбурге в 10 утра и едете в Москву. Возвращаете ее тоже в 10 утра через неделю, в 11 у вас самолет. Хотя вычитание timestamptz покажет вам что вы использовали машину 7 дней и 2 часа, денег с вас скорее всего за эти лишние 2 часа не возьмут, потому что это "абстрактные" 10 утра, и в абстрактных часах прошло только 7 дней. То же было при переходе на зимнее время, если начало и конец разделены сменой времени.
В общем, я к тому что не надо слепо менять timestamp на tz. Надо подумать о бизнес логике вашего приложения и что стоит за этим значением. Иногда нужен тупой timestamp.
Finesse
09.11.2023 16:00+5Очень не однозначный кейс. Пользовались машиной 170 часов, но почему должны платить за 168? Так со сменой часовых поясов или переводом времени может вообще получиться отрицательное время использования, и арендодатель останется должен клиенту.
mentin
09.11.2023 16:00+1Пользовались машиной 170 часов, но почему должны платить за 168?
Потому что на 7 дней вы берете машину не с почасовой оплатой, а дневной. И с 10 до 10 прошло 7 дней, а вот с 10 до 12 того же дня уже было бы 8.
Так же и с гостиницами, если заселение с 3х часов, а съезд в 11 утра, то при смене времени вы получите на час больше или меньше времени, но никого это не волнует, стоить это будет столько же. Это абстрактные 3 и 11.
Finesse
09.11.2023 16:00Согласен, кейсы имеют место быть. Тут в базе лучше хранить даты без времени, но можно придумать кейс где время тоже нужно (например, поздний выезд из гостиницы).
iamkisly
09.11.2023 16:00-2Я понял о чем написана статья, даже сталкивался с этой проблемой, но не увидел четкого вывода из проблемы. Потому что не использовать голый timestamp это не вывод, я сталкивался с ситуацией, где мы в находясь в одном часовом поясе и обмазавшись часовыми поясами получали +6 потому что корень проблемы в несогласованности и отсутствии соглашения внутри команды.
PS. Имхо клиент имеющий прямой доступ к БД это небезопасно и антипаттерн, и такого быть не должно.. хотя я вполне такое видел и даже у себя в подразделении, в основном у любителей нашлепать гуй в делфи или winforms. И уж тем более клиент в Челябинске не должен устанавливать часовой пояс MSK, это глупо.
mayorovp
09.11.2023 16:00У timestampz есть обратная проблема, которая уже достала. А именно, невозможно понять в какой таймзоне клиент показывает время.
Это может быть UTC, это может быть время сервера или местное время клиента. И самое весёлое - когда, отлаживая проблему с таймзонами, ты делаешь неверное предположение о поведении своего клиента и в итоге в упор не видишь ошибку в БД.
Хорошо хоть всегда можно сделать `select now()`. Но обычно до этого доходит довольно поздно...
Legushka
09.11.2023 16:00Помню раньше ржд билеты были все по московскому времени. Может быть это единственное место где можно было использовать without time zone.
losse_narmo
09.11.2023 16:00+1Никогда не забуду как хотел купить билет на аэроэкспресс на 00:30 через сайт, а в итоге поехал и купил в кассе, так как не мог понять на какое число брать билет
Как оказалось у них 00:30 10-го августа считается все-таки 9-м числом (то есть дата переключается в момент ночного перерыва поездов, а не в полночь)
Fedorkov
09.11.2023 16:00+2Я однажды видел билет на поезд на 3:30 ночи, при том что в 3 часа стрелки переводились на час вперёд.
WINHACK777
09.11.2023 16:00По мне так наоборот именно применение timestamp without time zone позволяет снять большинство проблем, если речь идет о применении даты/времени для фиксации или планирования событий на стороне БД. Так гораздо проще реализовать дальнейшую обработку данных (условия, завязанные на дату/время и т.д.) в процедурах.
hackimov
09.11.2023 16:00+9Сколько лет обсуждают уже тему хранения времени. Ничего лучше чем работы с бд в UTC не существует. Вся трансформация должна находиться на бэкэнде приложения.
Схема должна быть такой:
БД-UTC , Backend , front-end transform UTC to locale
Front-end transform locale to UTC, backend, БД-UTC
cortl
09.11.2023 16:00+4Первое, на чём стоит застолбиться - это то, что за точку отсчёта лучше всего принять UTC.
Учавствовал в двух проектах со временем и оба с прицелом на мировой охват.
В первом про это совсем не думали и упёрлись, когда начали составлять и тестировать расписания: внутридневные, недельные, месячные. Пришлось срочно перекраивать работу со временем. Благо, что на тот момент ещё не было прома.
Второй проект давно уже в проме и в нём точка отсчёта МСК. Это боль.
GerrAlt
09.11.2023 16:00Выше была рассмотрена ситуация, когда клиенты находятся в разных часовых поясах и из-за использования timestamp without time zone пропадает возможность определить время события.
Очень громкое заявление, мне кажется, если соблюдать просто правило (вроде как достаточно известное) "в БД только UTC" то вообще никакой проблемы нет - вы всегда пишите в UTC и вы всегда можете точно сказать когда что произошло. Если вам нужно клиенту показывать в какой-то его таймзоне у вас есть очень много вариантов как добиться результата, в случае когда вы таймзоны тащите в БД все значительно хуже.
Fedorkov
09.11.2023 16:00+2UTC — это тоже таймзона.
Without timezone означает, что фактический момент времени неизвестен или безразличен (как в случае с будильником). Можно использовать его для хранения UTC, но это использование не по назначению.
GerrAlt
09.11.2023 16:00Поправьте меня пожалуйста если я не прав, но UnixTimestamp по определению считается именно в зоне UTC
Batalmv
09.11.2023 16:00+2Перечитал пару раз.
Мне кажется, первая проблема - это "Вася из Челябинска", который напрямую идет в базу. Такие Васи уже лет 20 должны бодро строиться в колону, и выбрав главного с барабаном идти строго на "север", никуда не сворачивая
Логически.
Есть база. Она живет в своем времени и у нее все хорошо. Вот реально все хорошо, кроме возможно ситуации два раза в год, даже нет, один раз в год, когда время переводят назад (если эта база живет в местности, где есть летнее/зимнее время). Но это очень мелкий нюанс и я бы на него забил. И по идее, не так уж и важно, в каком часовом поясе она живет, скорее это уже реально какие-то игры с типами и т.д.
Есть бэкенд. У него тоже все неплохо, если он примет решение жить по времени с базой. Т.е. просто перенимает "позитивный" опыт и уверенно следует в зону "комфорта и непротиворечия с внешним миром".
Дальше есть клиент.
Допустим это Вася. Дальше есть варианты, которые зависят от понимания Васей, о каком времени идет речь. К примеру:
Выписка по счету. Тут логично показывать время по "банку", так как инфа в нем привязана к датам, а та - к операционным дням банка. Если конвертировать - будет допушено сознательное искажение информации. Поэтому клиент должен рисовать именно время "базы"
Время вылета рейса. Оно везде локальное, поэтому надо его и хранить не как время "базы", а чуть ли не как строку, так как тот, кто его дал, лучше знает "где сейчас самолет, где сейчас аэропорт" и что говорить опоздавшему клиенту.
Время последнего входа в систему. Тут можно и дать конвертнуть время в локальный часовой пояс клиента, так как подсознательно Вася видит это время с привязкой к себе, а значит и к тому часовому поясу
Примеров может быть больше. К примеру, если пилоты, которые меняют часовые пояса очень быстро. Есть моряки, которые делают это медленнее, но тоже вполне уверено.
-----------------
Я к чему. Надо понимать в первую очередь, кто и как смотрит на конкретное время "объекта".
И тех противоречий, которые есть в статье нет, так как они абстрактны. Ну дали запросы, увидели разное время. И? Пример то синтетический. Может это и нормально
Heggi
09.11.2023 16:00+5Наверное было бы логичнее, если бы timestamptz назывался timestamputc - timestamp by utc. Что однозначно бы говорило, что метка времени хранится по UTC.
Rampages
09.11.2023 16:00+1Пару лет назад был случай у сестры, в билете из Москвы в %default_city% стояло не то время. Т.е. пришел билет ввиде сгенерированного PDF файла в почту с неправильным временем, по приезду в аэропорт сказали "сорян, ваш самолет только что улетел" ???? (приехали примерно в обед, а вылет стоял примерно на 16:40) пришлось покупать новый билет за 40к рублей, а по старому писать в авиакомпанию чтобы вернули деньги. Из-за неправильного времени потеряны время и деньги клиента, а также авиакомпании, плюс репутационный ущерб компании и моральный ущерб клиенту. Мне в то время друг сказал что это мы лохи и надо было заранее заходить и проверять время вылета/прилета и вообще лучше не доверять системам, а позвонить в аэропорт и спросить ????
Интересно где была ошибка? Генерация PDF происходила по времени клиента находящегося в другом часовом поясе? или была по времени авиакомпании сервера, которой находились не в Москве? В билетах же всегда должны стоять время и дата аэропорта вылета? Да и не сказать что это было массовое явление и возможно как-то связано с клиентом и его локальным временем на момент оформления/покупки билета.
AntoineLarine
09.11.2023 16:00+1Ещё бывают даты без времени. Например, "договор вступает в силу с 01.12.2023". А в UTC это сколько будет? Понятно, что надо хранить дату отдельно, тайм-зону отдельно, и аккуратно всё приводить к UTC при сравнениях. Главное, об этом думать заранее, при проектировании системы.
nApoBo3
09.11.2023 16:00-5Сам пришёл к выводу, если дата и/или время прямо дико критичны их нужно хранить руками, отдельно, год, месяц, день, час, минута, таймзона. И так же передавать до конечного клиента, а там уже преобразовывать в человек читаемый вид. Иначе рано или поздно кто-нибудь, где-нибудь, с таймзоной накосячит.
Fedorkov
09.11.2023 16:00+1договор вступает в силу с 01.12.2023
Сила договора
бродит по планетеследует за сменой дат.
withkittens
09.11.2023 16:00-1Очень много комментариев в духе "зачем мне эти ваши тайм зоны, у меня всегда UTC, мне timestamp (without time zone) - норм". Официальная вики постгреса однозначно не рекомендует так делать.
nApoBo3
09.11.2023 16:00+1Очень интересно вы проблему доверия к данным с клиента свели к конфликту timestamp vs timestampz.
Клиент вам и в timestampz может на гадить, что с этим делать? Еще один тип данных придумать trustedtimestampz?
Вообще проблема временных зон она большая и в ней столько граблей, я бы с удовольствием целую книгу по этой теме прочитал :)
И к сожалению timestampz ее не решает, на мой взгляд он вносит только дополнительную путаницу, поскольку становиться окончательно не ясно как выглядело время в виде timestamp( 1699617564 ) в каждой из точек.
APh
09.11.2023 16:00-1Извините за отклонение от красной линии статьи! Это — так, для общего развития.
Кроме Индии и Пакистана дробные смещения временных зон имеют и много др. стран. Например, Непал, Мьянма, Афганистан, Бангладеш...Также, отмечу, что GMT и UTC — не синонимы!
Для простоты считайте, что GMT больше не используется.
Есть так называемое Всемирное время (UT, Universal Time), введённое в 1925 году. Вернее, многие версии всемирного времени (UT0, UT1, UT1R, UT2, UT2R и UTC), которые основаны на вращении Земли относительно далёких небесных объектов (звёзд и квазаров), используя коэффициент масштабирования и другие подстройки для того, чтобы быть ближе к солнечному времени.Есть также международное атомное время (TAI, фр. Temps Atomique International), которое является очень стабильным эталоном, не имеющим суточных, годичных, вековых и прочих колебаний, связанных с движением Земли. Однако, он не подходит астрономам именно поэтому.
Таким образом, солнечное и эфемеридное время (ET) имеют сдвиг относительно атомного, но т. к. атомное удобно и стабильно, то его используют, как шкалу для других синтетических времён (типа, TDB, Barycentric Dynamical Time), где взяли сдвиг нульпункта эфемеридного времени относительно атомного, а секунду положили равной атомному, чтобы не мучаться.Есть ещё несколько разных времён, но про них не в этом комментарии.
Virviil
Думаю что большинство читателей этой статьи используют базу для крудошлепства, и в этом плане у нее есть только один клиент - бэк.
Использование timestamp с хранением всех времен в utc полностью беспроблемное решение при таких условиях.
Во вьюшках utc переводится в нужную зону относительно клиента. Внутри бэка нету никаких разночтений и путаницы при операциях с датами и временем.
dmserebr Автор
Ну это классика: сегодня может казаться, что клиент только бекенд, а завтра появляется что-то новое и никому даже в голову не приходит, что это может что-то сломать.
Аргумент был бы валиден только в случае, если у timestamptz есть какой-то оверхед, но его нет.
Virviil
У timestampz есть когнитивный оверхед когда начинаешь считать «вчера это было или сегодня» на бэке. Или когда у third party api принимается дата в utc. И эти баги случаются гораздо раньше чем «гипотетический другой клиент» который обычно не появляется никогда
dmserebr Автор
На бекенде timestamptz, если нужно, без проблем преобразуется в локальный (в системной таймзоне сервера) и там, где надо, работают с локальным. А вообще есть типы, которые напрямую маппятся на timestamptz (например, OffsetDateTime в Java) и в коде бекенда тоже ими можно оперировать.
Про "гипотетический клиент" — я бы сказал, это довольно сильное заявление, особенно в больших проектах на много человек. Даже если вы стараетесь следить, чтобы все insert выполнялись исключительно из кода бекенда, гарантий дать невозможно и кто будет после вас с кодом работать — тоже вопрос. При всем этом есть простое решение, как сделать надежно.
n43jl
На моей практике в 90% случаях бекенду вредно знать о timezone, поэтому всегда где-можно должно быть UTC.
Класть utc в timestamp и всегда заменять now() на "now() AS TIME ZONE 'utc'" или использовать timestamptz которое всегда конвертится к utc на стороне приложения - по моему одинаково. Имхо OffsetDateTime в Java в 90% анти-паттерн, зачем-то многие добавляют и процессят информацию о TZ когда она совершенно нерелевантна, и нужно использовать Instant (UTC).
TerrorDroid
С куда большей вероятностью завтра никогда не наступит
Ivan22
- Завтра никогда не наступит, после нас хоть потоп, живем один раз...
- А можно нам другого архитектора?
krabdb
"В Гонконге уже завтра"!
M_AJ
Когда разные клиенты напрямую лезут в базу всегда есть вариант, что "завтра", а точнее при внесении изменений, кто-то о них не узнает, и что-то из-за этого сломается. Чтобы этого избежать, достаточно оставить одного клиента, который будет точно знать как интерпретировать записанное в базе, а другим отдавать через документированное API
Gromilo
Я тот самый крудошлёп!
У меня обычно так:
Время нужно хранить в UTC, а браузер отобразит правильно, например сообщение чата и время создания поста
Время нужно хранить в UTC и у пользователя/устройства/города есть часовой пояс и сервер посчитает как правильно. Например, даты в письмах.
Время нужно хранить ни в чём, т.к. это расписание соревнований в Самаре или в Челябинске и если соревнование начинается в 11, значит везде 11 часов не смотря на часовой пояс клиента.
Для этого я использовал timestamp, но в 6 версии Npgsql решили, что timestamp нужно переводить в локальное время и я пока сижу на
AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);По идее нужно переезжать на
DateTimeOffset, но я пока не разбирался как жить с тем, что всегда естьOffset, который мне не нужен.Heggi
В 7 версии Npgsql стало все веселее. Но, в целом, это все не сложно.
С DateTimeOffset работать просто.
Достаешь из БД данные? В DateTimeOffset время по UTC. Если надо, добавляешь смещение и получаешь время в нужном часовом поясе. Если не нужно (браузер сам умный), то так и отдаешь.
Сохраняешь в БД? Сделай offset = 0, иначе npgsql просто не даст это сохранить в БД (у нас используется gRpc, там Timestamp и так только в UTC передается, так что для нас это вообще не проблема. Только при работе со сторонними API надо быть внимательнее)
withkittens
Подумайте насчёт NodaTime, библиотека разделяет "разные времена" по разным типам:
Время сообщения чата или создания поста - это
InstantЕсли нужно помнить время в часовом поясе, то это
ZonedDateTimeРасписание соревнований - это
LocalDateTimeили дажеLocalTimeNpgsql/EF Core библиотеку поддерживают.
Fedorkov
NodaTime - это самое надёжное решение для работы с часовыми поясами (и особенно с историей их изменений).
Но ещё надёжнее на уровне API отказаться принимать не-UTC время, тогда будет достаточно стандартного DateTimeOffset.
semmaxim
Тут может быть проблема со временем в будущем. Кто-то задаёт команду "разбудить в 8 утра" или "напомнить о совещании завтра в 10:00". Это уходит в базу в utc, а потом в поясе клиента переводят часы на летнее время. Всё, все эти utc timestamp указывают не неправильное время.
Virviil
Совещание это отличный пример.
Например, я забил совещание с коллегой завтра на 10:00 (у себя)
У меня часы перевели на летнее время, а у него - нет.
В результате ваш хитрый софт покажет наши совещания с часовой разницей.
Но фишка то в том, что если завтра у меня часы переводят на летнее время и я делаю совещание на завтра, то у меня сохраниться ПРАВИЛЬНЫЙ utc с завтрашним летним смещением
semmaxim
Нет. Совещание на завтра обрабатывается одним и тем же бэкендом, так что у обоих будут либо переведены часы либо нет. Да даже если на разных серверах - тот факт, что у коллеги часы на бэкенде не переведены - это просто баг и ошибка. Учёт летнего/зимнего времени - это необходимое условие правильной работы со временем. И не перевести часы всё равно что в один момент решить, что в часе 80 минут.
ПРАВИЛЬНЫЙ он будет, если перевод часов можно предсказать. Но мало ли что будет завтра. У нас тут в Волгоградской области за пару лет отменяли летнее/зимнее время, переводили часовой пояс в Московский и всё как-то быстро и неожиданно.
Если смотреть на это абстрактно, то в логическом объекте "совещание завтра в 10 утра" нет чёткой временной метки в нашем трёхмерном континууме. Это просто некая абстрактная сущность "10 утра", которая не связана жёстко со временем нашей вселенной. Она может двигаться на временной оси, она зависит от субъекта наблюдения - она содержит в себе не просто число "10" но и то, как и кто её интерпретирует и по каким законам. Вы предлагаете по сути отрезать все дополнительные характеристики и сохранять в базу просто некое число. А характеристики брать извне, из других источников. Которые вдруг могут потерять синхронизацию с этим числом "10" в БД (например, когда отменяют переход на летнее время).
Virviil
Еще раз объясняю:
Неделю назад у меня было совещание в 12 утра с Москвой из Израиля. У меня 12 утра и в Москве 12 утра. В выходные у нас поменяли время на зимнее, а в Москве - не поменяли. А теперь скажи, у меня это совещание должно переехать на 11, чтобы в Москве оно осталось в 12, или у меня должно остаться в 12, чтобы в Москве оно теперь начиналось в 13?
semmaxim
Откуда ж мне знать? Это в бизнес-логике должно быть указано, местное время какого региона надо использовать. Спор то вообще не об этом. Как раз Вашем примере если хранится в БД как utc timestamp, то непонятно, что с ним делать и нужно ли куда-то двигать. А если хранится в БД как "10 часов по часовому поясу Москвы", то сразу понятно, когда именно это событие должно произойти.
BearOff
Там ниже ещё одна ветка с таким же точно обсуждением будущих дат и DST.
Я там привёл хорошие ссылки на SO, повторю тут тоже: раз, два.