为什么我的时间戳在时区中移动？ [英] Why is my timestamp shifted in timezone?

问题描述

我在PostgreSQL 9.1数据库中有一个 timestamp没有时区列的日期：

2012-11-17 13:00:00

它的意思是用UTC表示，已经通过选择它作为UNIX时间戳（ EXTRACT epoch ）进行了验证。

  int epoch = 1353157200; // from database 
 Date date = new Date（（long）epoch * 1000）; 
 System.out.println（date.toGMTString（））; //输出17十一月2012 13:00:00 GMT 
  

但是，当我使用JPA /休眠，事情出错了。这是我的映射：

  @Column（nullable = true，updatable = true，name =startDate）
 @Temporal（TemporalType.TIMESTAMP）
私人日期开始; 
  

Date 然而， ：

17 Nov 2012 12:00:00 GMT

为什么会发生这种情况，更重要的是，我该如何阻止它？

请注意，我只是希望将时间点存储为通用的（如 java.util.Date ），除了我显然不希望它们破坏我的数据外，我不关心时区。

正如您可能已经推断的那样，连接到数据库的客户端应用程序使用UTC + 1（荷兰）。 b
$ b

另外，对于列类型没有时区的时间戳是由Hibernate在自动生成模式时创建的。如果这可能是带时区的时间戳而不是？

解决方案

数据库不提供时区信息，那么JDBC驱动程序应该将它视为在JVM的本地时区内（请参阅 PreparedStatement.setDate（int，Date））：

使用运行应用程序的虚拟机的默认时区将指定参数设置为给定的java.sql.Date值。

Javadoc和JDBC规范没有明确地说出关于 ResultSet 等的任何内容，但要保持一致，大多数驱动程序也将将该规则应用于从数据库中检索的日期。如果你想显式控制使用的时区，你需要使用各种 set / getDate / Time / Timestamp 方法，它们也接受 Calendar 对象。

有些驱动程序还提供连接属性，允许您指定转换为/从数据库转换时使用的时区。

I have this date in a PostgreSQL 9.1 database in a timestamp without time zone column:

2012-11-17 13:00:00

It's meant to be in UTC, and it is, which I've verified by selecting it as a UNIX timestamp (EXTRACT epoch).

int epoch = 1353157200; // from database
Date date = new Date((long)epoch * 1000);
System.out.println(date.toGMTString()); // output 17 Nov 2012 13:00:00 GMT

However, when I read this date using JPA/Hibernate, things go wrong. This is my mapping:

@Column(nullable=true,updatable=true,name="startDate")
@Temporal(TemporalType.TIMESTAMP)
private Date start;

The Date I get, however, is:

17 Nov 2012 12:00:00 GMT

Why is this happening, and more importantly, how can I stop it?

Note that I just want to store points in time, universally (as java.util.Date does), and I couldn't care less about timezones, except that I obviously don't want them to corrupt my data.

As you've probably deduced, the client application which connects to the database is in UTC+1 (Netherlands).

Also, the choice for the column type timestamp without time zone was made by Hibernate when it automatically generated the schema. Should that maybe be timestamp with time zone instead?

解决方案

If a database does not provide timezone information, then the JDBC driver should treat it as if it is in the local timezone of the JVM (see PreparedStatement.setDate(int, Date)):

Sets the designated parameter to the given java.sql.Date value using the default time zone of the virtual machine that is running the application.

The Javadoc and the JDBC specification do not explicitly say anything about ResultSet etc, but to be consistent most drivers will also apply that rule to dates retrieved from the database. If you want explicit control over the timezone used, you will need to use the various set/getDate/Time/Timestamp methods that also accept a Calendar object in the right timezone.

Some drivers also provide a connection property allowing you to specify the timezone to use when converting to/from the database.

这篇关于为什么我的时间戳在时区中移动？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！