如何正确将SQL Timestamp转换为UTC时区的Instant？解决偏移问题

数据库字段类型为timestamp without time zone，存储的是UTC时间，但用JDBC读取得到的java.sql.Timestamp值是2024-05-15 15:12:44.0，调用toInstant()转换为Instant后变成2024-05-15T12:12:44Z，出现3小时时区偏移，期望转换后保持原UTC时间不变。

示例代码 #

package ru.goldenage.map.repository.postgres.impl;

import ru.goldenage.map.entity.postgres.CalculatedCar;

import javax.persistence.EntityManager;
import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.Instant;

@Slf4j
@Repository
@AllArgsConstructor
public class CarDAOImpl {
    
    private DataSource dataSource;
    private JdbcTemplate postgresTemplate;
    private EntityManager em;

    public List<CalculatedCar> getCalculatedCarsBy(String minutes) throws SQLException {
        String sql = "SELECT tmi.p_id                                                   AS id,"
        + "tmi.p_park_id                                                                AS parkId, "
        + "tmi.p_gosnumber                                                              AS gosnumber,"
        + "tmi.p_park_name                                                              AS parkName,"
        + "taotr.p_lon                                                                  AS lon,"
        + "taotr.p_lat                                                                  AS lat,"
        + "p_azimuth                                                                    AS vector,"
        + "p_speed                                                                      AS speed,"
        + "tmi.p_mark_name                                                              AS markName,"
        + "tmi.p_mark_id                                                                AS markId,"
        + "tmi.p_transport_type                                                         AS TT_Title,"
        + "mt.p_group_id                                                                AS groupTransportType,"
        + "mt.p_title                                                                   AS titleTransportType,"
        + "org.p_title                                                                  AS organization,"
        + "taotr.p_time                                                                 AS time,"
        + "p_kilometers                                                                 AS kilometers,"
        + "coalesce(substring(tum.p_name from 0 for position(' км' in tum.p_name)), '') AS road_name,"
        + "coalesce(tmi.p_navigator, '-')                                               AS stationNum,"
        + "tmi.p_machine_type "
        + "FROM t_machine_info AS tmi "
          + "LEFT JOIN t_machine_type AS mt on tmi.p_machine_type = mt.p_id "
          + "LEFT JOIN t_organization AS org on tmi.p_organization = org.p_id, "
      + "(SELECT * FROM t_auto_on_the_roads "
       + "WHERE p_id in (SELECT max(p_id) FROM t_auto_on_the_roads "
                      + "WHERE p_time >= (current_timestamp AT TIME ZONE 'UTC') - (?1 * CAST('1 minutes' AS interval)) "
                      + "GROUP BY p_id_auto)) AS taotr "
          + "LEFT JOIN t_unit_members AS tum ON tum.p_id = taotr.p_name_road "
 + "WHERE taotr.p_id_auto = tmi.p_id;"

       List<Object[]> resultList = em.createNativeQuery(sql)
                                     .setParameter(1, Integer.parseInt(minutes))
                                     .getResultList();

      List<CalculatedCar> calculatedCars = new ArrayList<>(resultList.size());

      resultList.forEach(result -> {
             BigDecimal id = (BigDecimal)result[0];
             Integer parkId = (Integer)result[1]; 
             String gosNumber = (String) result[2];
             String parkName = (String) result[3];
             Double lon = (Double) result[4];
             Double lat = (Double) result[5];
             Integer vector = (Integer) result[6];
             Integer speed = (Integer) result[7];
             String markName = (String) result[8];
             Integer markId = (Integer) result[9];
             String title = (String) result[10];
             BigDecimal groupTransportType = (BigDecimal) result[11];
             String titleTransportType = (String) result[12];
             String organization = (String) result[13];
             Timestamp tmp = (Timestamp) result[14];
             System.out.println("TIMESTAMP TIME = " + tmp);
             Instant timeUTC = tmp.toInstant();
             System.out.println("INSTANT TIME = " + timeUTC);
             String kilometers = (String) result[15];
             String roadName = (String) result[16];
             String stationNum = (String) result[17];
             BigDecimal machineType = (BigDecimal) result[18];

             CalculatedCar car = CalculatedCar
                                    .builder()
                                    .id(id)
                                    .parkId(parkId)
                                    .gosnumber(gosNumber)
                                    .parkName(parkName)
                                    .lon(lon)
                                    .lat(lat)
                                    .vector(vector)
                                    .speed(speed)
                                    .markName(markName)
                                    .markId(markId)
                                    .TTTitle(title)
                                    .groupTransportType(groupTransportType)
                                    .titleTransportType(titleTransportType)
                                    .organization(organization)
                                    .timeUTC(timeUTC)
                                    .kilometers(kilometers)
                                    .roadName(roadName)
                                    .stationNum(stationNum)
                                    .machineType(machineType)
                                    .build();
             calculatedCars.add(car);
        });

      return calculatedCars;
    }
}

问题核心代码段 #

Timestamp tmp = (Timestamp) result[14];
System.out.println("TIMESTAMP TIME = " + tmp);
Instant timeUTC = tmp.toInstant();
System.out.println("INSTANT TIME = " + timeUTC);

环境版本 #

• Java 11
• Spring 2.1.2 RELEASE
• PostgreSQL 9.6.15

解决方案 #

问题原因 #

java.sql.Timestamp的toInstant()方法默认会把时间视为JVM本地时区的时间，再转换为UTC格式的Instant。而数据库中timestamp without time zone类型本身不带时区信息，JDBC驱动读取时会自动将其适配为当前JVM的本地时区时间，导致最终转换出的Instant和原UTC时间出现偏移。

解决办法 #

1. 代码层面修正转换逻辑（最快见效）

不要直接用toInstant()，而是明确指定将Timestamp视为UTC时间来转换：

// 替换原转换代码
Timestamp tmp = (Timestamp) result[14];
Instant timeUTC = tmp.toLocalDateTime().atZone(ZoneId.of("UTC")).toInstant();

这样就能把数据库里的UTC时间正确转换为对应的Instant对象，不会出现时区偏移。

2. 数据库字段类型优化（推荐长期方案）

如果业务允许，把字段类型从timestamp without time zone改为timestamp with time zone（PostgreSQL里缩写为timestamptz）。这种类型会明确存储时区信息，JDBC读取时会直接识别为UTC时间，避免时区歧义。

3. JDBC连接参数配置

在PostgreSQL的JDBC连接URL中添加serverTimezone=UTC参数，告诉驱动数据库里的时间是UTC时区的：

jdbc:postgresql://localhost:5432/your_db?serverTimezone=UTC

这样驱动读取timestamp without time zone时会默认按UTC解析，后续转换Instant时就不会出错。

内容的提问来源于stack exchange，提问作者Ilya Rogatkin