Как получить временные метки UTC из JDBC + postgreSql timestamp?

В PostgreSQL я создал таблицу, подобную этой:

create table myTable (
    dateAdded timestamp(0) without time zone null default (current_timestamp at time zone 'UTC');
)

Я выбираю "без часового пояса", потому что знаю, что все временные метки, с которыми работает мое приложение, всегда являются UTC. Насколько я получил документацию, единственное отличие от "timestamp" заключается в том, что я могу предоставлять значения в других часовых поясах, которые затем будут преобразованы в UTC. Однако я хочу избежать таких автоматических преобразований, потому что они вряд ли могут принести пользу, если я знаю, что мои значения - это UTC.

Когда я добавляю новую запись в свою тестовую таблицу и просматриваю содержимое таблицы с помощью pgAdmin, я вижу, что дата вставки была правильно сохранена в формате UTC.

Однако, когда я пытаюсь выбрать значения, используя JDBC, значение получает 2 часа. Я нахожусь в UTC + 2, так что похоже, что JDBC предполагает, что дата в таблице не является меткой времени UTC, а UTC + 2 timestamp вместо этого и пытается конвертировать в UTC.

Некоторые поисковые запросы показали, что стандарт JDBC диктует что-то о преобразовании в/из текущего часового пояса, но это можно предотвратить, предоставив Calander для вызова getTimestamp/setTimestamp. Однако поставка календаря не имела никакого значения. Вот мой конвертер MyBatis/Jodatime:

@MappedTypes(DateTime.class)
public class DateTimeTypeHandler extends BaseTypeHandler<DateTime> {
    private static final Calendar UTC_CALENDAR = Calendar.getInstance(DateTimeZone.UTC.toTimeZone());

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
            DateTime parameter, JdbcType jdbcType) throws SQLException {
        ps.setTimestamp(i, new Timestamp(parameter.getMillis()), UTC_CALENDAR);
    }

    @Override
    public DateTime getNullableResult(ResultSet rs, String columnName)
            throws SQLException {
        return fromSQLTimestamp(rs.getTimestamp(columnName, UTC_CALENDAR));
    }
    /* further get methods with pretty much same content as above */

    private static DateTime fromSQLTimestamp(final Timestamp ts) {
        if (ts == null) {
            return null;
        }
        return new DateTime(ts.getTime(), DateTimeZone.UTC);
    }
}

Какой правильный способ получить временные метки UTC из источника времени JDBC + PostgreSQL?

Ответы

Ответ 1

Решение

Установите UTC в качестве часового пояса по умолчанию для вашей JVM -Duser.timezone=UTC или установите для всей ОС значение UTC.

Фон

В Postgres и TIMESTAMP, и TIMESTAMP WITH TIMEZONE хранятся одинаково - количество секунд, прошедшее с эпохи Postgres (2000-01-01). Основное отличие состоит в том, что делает Postgres, когда сохраняет значение метки времени, например 2004-10-19 10:23:54+02:

  • без TZ +02 просто убирается
  • с TZ выполняется коррекция -02, чтобы сделать ее UTC

Теперь интересно, когда драйвер JDBC загружает значение:

  • без TZ сохраненное значение смещается пользователем (JVM/OS) TZ
  • с TZ значение считается UTC

В обоих случаях вы получите объект java.sql.Timestamp с пользовательским TZ по умолчанию.

Часовые пояса

Метки времени без TZ довольно ограничены. Если к вашей базе данных подключены две системы с разными значениями TZ, они будут интерпретировать временные метки по-разному. Поэтому я настоятельно советую вам использовать TIMESTAMP WITH TIMEZONE.

Обновление

Вы можете сообщить JDBC, какой тип TZ следует использовать при чтении метки времени через ResultSet#getTimestamp(String, Calendar). Выдержка из JavaDoc:

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

Ответ 2

Решение, предложенное Pavel (добавление jvm param '-Duser.timezone = UTC'), наверняка является лучшим вариантом, если у вас нет доступа к системе, это не всегда можно сделать.

Единственный способ, которым я нашел, - преобразовать метку времени в epoch в запросе и прочитать ее как long.

SELECT  extract(epoch from my_timestamp_colum at time zone 'utc')::bigint * 1000 
            AS my_timestamp_as_epoc
FROM    my_table

Затем прочитайте его в простом JDBC с помощью

ResultSet rs = ...
long myTimestampAsEpoc = rs.getLong("my_timestamp_as_epoc");
Date myTimestamp = new Date(myTimestampAsEpoc);

С iBatis/MyBatis вам нужно обработать столбец как Long, а затем преобразовать его вручную в Date/Timestamp. Неудобный и уродливый.

Обратная операция в PostgreSQL может быть выполнена с помощью

SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + 
       1421855729000 * INTERVAL '1 millisecond'

Тем не менее, спецификация JDBC не говорит, что возвращенная временная метка должна или не должна сдвигать метку времени в пользовательский часовой пояс; НО, так как вы определили столбец таблицы как "метка времени без часового пояса", я бы не ожидал сдвигов во времени, но записанная эпоха была просто завернута в java.sql.Timestamp. По моему мнению, это ошибка в драйвере PostgreSQL JDBC. Будучи проблемой на этом уровне, возможно, не так много, что можно сделать без доступа к системе.

Ответ 3

Есть несколько приемов, специфичных для драйвера Postgres JDBC

Смотрите https://jdbc.postgresql.org/documentation/head/java8-date-time.html

Таким образом, при чтении вы можете сделать

    Instant utc =resultSet.getObject("dateAdded",LocalDateTime.class).toInstant(ZoneOffset.UTC);

Если вы используете пул соединений, такой как Hikari, вы также можете указать часовой пояс, используемый каждым соединением, установив connectionInitSql =установить часовой пояс "UTC"