Добрый день Для вывода графиков в веб-приложении я функцией generate_series генерирую

даты от и до с интервалом в 1 день, затем лефтджойню табличку с данными (нужно показать кол-во добавленных записей по дням) и группирую по дате

Все бы ничего, если бы не часовые пояса. В самой базе данные хранятся в UTC, но при коннекте с веб-приложения я делаю SET TIMEZONE = пользовательская таймзона и из-за этого по дате данные джойнятся некорректно

Что посоветуете?

> В самой базе данные хранятся в UTC, Изменить вот это на нормальное хранение (или придётся мучиться). В сторону: что-то сегодня прямо день неправильно работающих с timestamp-ами. ;)

А чем хранение в UTC не нормальное, если к БД коннектятся из разных частей мира?

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29 Вообще прочитайте весь этот раздел.

Я уже как-то долго с одним человеком спорил об этом (рассматривая конкретно мой случай) и в итоге он согласился, что я прав. Это было долго, я не готов повторять это снова 🙂

Это просто потрясающе. Т.е. у Вас проблемы с тем, что при правильном подходе было бы тривиально, но Вы правы? ;) > и в итоге он согласился, что я прав А никто, кто знает, как работать с датой/временем, не согласился бы, понимаете? Просто для информации: приведённая ссылка — это не предмет для спора, это описание правильных подходов.

Спасибо, это познавательно в любом случае

Не за что. Вы могли бы, конечно, показать \d таблицы / пример данных / нужного результата — всё это вполне решаемо и при неправильном хранении, только писать [намного] больше, и ошибиться куда легче.

Я вам, кстати, тоже могу ответить ссылкой с авторитетного ресурса (42 плюсика напротив коммента, все же, о чем-то говорит): https://dba.stackexchange.com/questions/59006/what-is-a-valid-use-case-for-using-timestamp-without-time-zone Это я не пытаюсь сказать, что вы не правы, просто бывают случаи, когда уместно хранить без таймзоны и у меня как раз тот случай

Кхе-кхе. Во-первых, "90% оf Stack overflow answers is BS." © И даже там: "Now, with those two down, I can came with only one good reason to use timestamp WITHOUT time zone. That is when you want to store events in the future and that some kind of alert must be triggered when we got to that time." — и это тоже неправильно, строго говоря (но почти всегда "сойдёт"). Во-вторых, правильность определяется не общественным мнением, авторитетом и т.п. ерундой. ;) Но даже если Вы падки на такие вещи, Вы сравниваете статью от сообщества PostgreSQL, написанную и проверенную, в том числе, несколькими основными разработчиками, с ответами "левых" людей и их "авторитетными" голосами. > когда уместно хранить без таймзоны и у меня как раз тот случай К хранению / работе с "гражданским" временем в прошлом есть только один правильный подход (что касается планируемых событий — см. выше), и весь postgres "заточен" именно на него (используя что-то другое, Вы будете бороться с postgres, и наступать на грабли в неожиданных местах... как будто в timestamps самих по себе мало трудностей). Чем Ваш случай такой уникальный, ради любопытства?

https://www.monkeyuser.com/2020/election/

А чем при работе с timestamptz объясняется тот момент, что при корректно настроенной на клиенте time zone, полностью совпадающей с time zone клиента, время в столбце с типом timestamptz не является реальным временем?

Я не понял вопроса. Что Вы имеете в виду?

Iurii Shaporenko

Вы можете текстом показать?

Iurii Shaporenko
Клиентский часовой пояс — Новосибирск. Серверный —...

Уберите каст к timestamptz, сделайте timestamp at time zone ...

Оно есть, это второй столбец как раз. Данные без явного указания часового пояса и с ним разные

Да, увидел

Вот так, к примеру, это должно выглядеть: SELECT now() AS sent_at_tz, now()::timestamptz AT time zone 'Asia/Novosibirsk' AS sent_at__with_tz_given, now()::timestamp AS sent_at_no_tz; sent_at_tz | sent_at__with_tz_given | sent_at_no_tz -------------------------------+----------------------------+---------------------------- 2021-07-02 18:48:43.506828+03 | 2021-07-02 22:48:43.506828 | 2021-07-02 18:48:43.506828 Обратите внимание на формат каждого поля — все timestamptz обязаны включать time zone при выводе, если её нет — что-то портит этот самый вывод.

Я вам еще более забавный артефакт могу показать SELECT now() AS now, now()::text as now_text 02.07.2021 21:53:09 2021-07-02 22:53:09.227937+07

Это явный "косяк" клиента, больше ничего — выкиньте его в мусорное ведро найдите, как там это настраивается. ;)

Зря вы так на Aqua Data Studio — ничего более удобного для выгрузок большого объема данных напрямую в Excel или сортировки этих уже полученных данных без повторного выполнения запроса внутри самой IDE (как это делают условные DataGrip, Toad, да тот же pgAdmin) и без необходимости листать эти результаты батчами по 100/500/1000/другое_очень_маленькое_количество строк Но вот это, видимо, действительно артефакт, и придется в конфигах копаться. Хотя, опять-таки, непонятно, почему одна и та же IDE на разных машинах ведет себя по-разному

Ну это уж кому что нравится, но проблема-то есть...

Покопался в issues у производителя IDE, нашел там ссылочку на postgresql.org, где такое поведение объясняется поведением java.sql.Time https://www.postgresql.org/message-id/alpine.BSO.2.00.1005190257480.27053@leary.csoft.net