java-timezone-example

Демо-приложение на базе Spring Boot 3 с демонстрацией сохранения временных отметок в PostgreSQL БД с сохранением временного сдвига.

Описание проблемы

В PosgtreSQL в столбцах с типом TIMESTAMP WITH TIME ZONE значение на самом деле хранится в UTC. Если сохраняемое значение принадлежит другой временной зоне, то перед сохранением значение принудительно переводится в UTC. Подробнее см. в документации к PostgreSQL.

Это приводит к потере информации о временном сдвиге после сохранения данных в БД.

Решение

Предложено использовать возможности ORM-фреймворка Hibernate 6, который используется в Spring Boot версии 3 и выше.

Данная версия Hibernate умеет сохранять поля с временными значениями в два поля таблицы: в одном собственно значение, в другом - значение временного сдвига. Для программиста задача сводится к созданию дополнительного столбца и добавлению следующей аннотации к полю в JPA сущности:

@TimeZoneStorage(TimeZoneStorageType.COLUMN)

Для того, чтобы корректно работала сортировка по исходному полю, было решено оставить его тип TIMESTAMP WITH TIME ZONE. В этом случае временные значения будут сортироваться согласно их UTC-представлению, что соответствует хронологическому порядку.

Дополнительное поле должно по умолчанию иметь название с суфиксом _tz и иметь тип INTEGER. Таким образом, наши поля в БД выглядят следующим образом:

ts    TIMESTAMP WITH TIME ZONE NOT NULL,
ts_tz INTEGER                  NOT NULL,

При выборке записей с сортировкой по полю ts Hibernate добавляет в секцию ORDER BY оба поля:

select *
from sample
order by ts, ts_tz

Конечно, для хронологического порядка добавление поля ts_tz не требуется, достаточно было бы сортировать только по полю ts, но так уж работает Hibernate. Возможно, это можно как-то подкорректировать.

Для эффективной сортировки индекс, создаваемый для поля ts, должен также включать поле ts_tz, в противном случае PostgreSQL не будет его использовать:

CREATE INDEX ix_sample_ts ON sample (ts, ts_tz);

Сборка приложения

./mvnw clean verify

Упаковка в Docker-контейнер

docker image build -t tzdemo:1.0.0 -t tzdemo:latest . 

Запуск приложения

docker compose up

При успешном запуске приложение будет слушать HTTP-соединения по порту 8080.

Тестирование

После запуска приложения создадим три записи с временными отметками из разных временных зон:

curl --location 'http://localhost:8080/api/samples' \
--header 'Content-Type: application/json' \
--data '{
    "ts": "2023-10-26T12:34:56+03:00",
    "val": "+3"
}' && \
curl --location 'http://localhost:8080/api/samples' \
--header 'Content-Type: application/json' \
--data '{
"ts": "2023-10-26T12:34:56+04:00",
"val": "+4"
}' && \
curl --location 'http://localhost:8080/api/samples' \
--header 'Content-Type: application/json' \
--data '{
"ts": "2023-10-26T12:34:56+05:00",
"val": "+5"
}'

Теперь прочитаем ранее созданные записи:

curl --location 'http://localhost:8080/api/samples'

Записи возвращаются в хронологическом порядке, значения временных сдвигов сохранены:

[
  {
    "ts": "2023-10-26T12:34:56+05:00",
    "val": "+5"
  },
  {
    "ts": "2023-10-26T12:34:56+04:00",
    "val": "+4"
  },
  {
    "ts": "2023-10-26T12:34:56+03:00",
    "val": "+3"
  }
]

Также проверим обратный порядок сортировки:

curl --location 'http://localhost:8080/api/samples?reversed=true'

Записи возвращаются в обратном хронологическом порядке:

[
  {
    "ts": "2023-10-26T12:34:56+03:00",
    "val": "+3"
  },
  {
    "ts": "2023-10-26T12:34:56+04:00",
    "val": "+4"
  },
  {
    "ts": "2023-10-26T12:34:56+05:00",
    "val": "+5"
  }
]

Проверим в консоли PostgreSQL, что при выборке записей в БД используется индекс:

explain analyze select * from sample order by ts, ts_tz;

QUERY
PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using ix_sample_ts on sample  (cost=0.15..61.95 rows=920 width=60) (actual time=0.023..0.027 rows=3 loops=1)
 Planning Time: 0.114 ms
 Execution Time: 0.081 ms
(3 rows)

Посмотрим, как выглядят наши данные в БД:

# select ts, ts_tz, val from sample;

           ts           | ts_tz | val 
------------------------+-------+-----
 2023-10-26 09:34:56+00 | 10800 | +3
 2023-10-26 08:34:56+00 | 14400 | +4
 2023-10-26 07:34:56+00 | 18000 | +5

Как видим, значение временного сдвига хранится в секундах.