Java MySQL Timestamp 时区问题 [英] Java MySQL Timestamp time zone problems

问题描述

我有一个 java.util.Date 对象，我需要将它以 UTC 格式插入 MySQL 中的日期时间字段.

I have a java.util.Date object, and I need to insert it into a datetime field in MySQL in UTC format.

java.util.Date date = myDateFromSomewhereElse;
PreparedStatement prep = con.prepareStatement(
    "INSERT INTO table (t1, t2) VALUES (?,?)");

java.sql.Timestamp t = new Timestamp(date.getTime());
prep.setTimestamp(1, t, Calendar.getInstance(TimeZone.getTimeZone("PST"));
prep.setTimestamp(2, t, Calendar.getInstance(TimeZone.getTimeZone("UTC"));
System.out.println(prep.toString());

这给了我准备好的 SQL 语句字符串:

Which gives me the prepared SQL statement string:

INSERT INTO table (t1, t2) VALUES ('2012-05-09 11:37:08','2012-05-09 11:37:08');

无论我指定的时区如何，返回的时间戳都是相同的时间戳.它忽略了我指定的带有时区的 Calendar 对象.发生了什么事，我做错了什么?

The timestamp returned is the same timestamp regardless of the timezone I specify. It's ignoring the Calendar object with timezone that I specify. What is going on and what am I doing wrong?

推荐答案

TimeZones 只是查看日期(即固定时间点)的不同方式.我在这里写了一个小例子(密切注意断言):

TimeZones are just different ways to view a date (which is a fixed point in time). I wrote a little example here (pay close attention to the assert):

// timezone independent date (usually interpreted by the timezone of 
// the default locale of the user machine)
Date now = new Date();

// now lets get explicit with how we wish to interpret the date
Calendar london =  Calendar.getInstance(TimeZone.getTimeZone("Europe/London"));
Calendar paris = Calendar.getInstance(TimeZone.getTimeZone("Europe/Paris"));

// now set the same date on two different calendar instance
london.setTime(now);
paris.setTime(now);

// the time is the same
assert london.getTimeInMillis() == paris.getTimeInMillis();

// London is interpreted one hour earlier than Paris (as of post date of 9th May 2012)
String londonTime = london.get(Calendar.HOUR) + ":" + london.get(Calendar.MINUTE);
String londonTZ = london.getTimeZone().getDisplayName(london.getTimeZone().inDaylightTime(london.getTime()), TimeZone.SHORT);
System.out.println(londonTime + " " + londonTZ);

// Paris is interpreted one hour later than Paris (as of post date of 9th May 2012)
String parisTime = paris.get(Calendar.HOUR) + ":" + paris.get(Calendar.MINUTE);
String parisTZ = paris.getTimeZone().getDisplayName(paris.getTimeZone().inDaylightTime(paris.getTime()), TimeZone.SHORT);
System.out.println(parisTime + " " + parisTZ);

此代码段的输出是(结果会因执行日期/时间而异):

The output to this snippet is (the result will be different depending on execution date/time):

8:18 BST
9:18 CEST

您在问题中的代码段根本没有对存储的日期做任何事情.通常数据库配置为本地时区.我建议存储一个额外的字段来表示解释日期时要使用的时区.

Your snippet in the question is simply not doing anything with regard to the date being stored. Usually databases are configured for a native TimeZone. I advise storing an extra field representing the TimeZone to be used when interpreting the date.

(通常)修改日期不是一个好主意(基本上是固定时间点之前/之后的几毫秒)，因为这将是一种有损修改，在一年中的不同时间点会有不同的解释(由于到夏令时).

It is not (generally) a good idea to modify dates (which are essentially just milliseconds before/after a fixed point in time) as this would be a lossy modification that would be interpreted differently at different points in the year (due to daylight savings time).

或者这个:http://puretech.paawak.com/2010/11/02/how-to-handle-oracle-timestamp-with-timezone-from-java/

这篇关于Java MySQL Timestamp 时区问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！