TimeZone在mysql和java中的差异 [英] TimeZone discrepancy in mysql and java

问题描述

我在mysql中有一个查询，比较2个这样的日期

I have a query in mysql which compares 2 dates like this

convert_tz(updatedDate,'+05:30','-05:00') < ?

convert函数返回美国时间中createddate列的值。当我在mysql查询浏览器中运行此查询时

the convert function returns the value of column createddate in US Time. when I run this query in mysql query browser like

convert_tz(updatedDate,'+05:30','-05:00') < '2013-04-14 09:30:00'

它给出了正确的值，例如

it gives me correct values for example

product    count
-------    ------
    A        123
    B        7

现在，我在java中使用PreparedStatement这样设置这个

Now, I am setting this in java using PreparedStatement like this

pst.setTimestamp(1, new java.sql.Timestamp(end.getTimeInMillis()));

                rs=pst.executeQuery();
                System.out.println("=====new Open Tickets Query executed=====");
                System.out.println(pst);

最后一行打印整个查询，值集

the last line prints the whole query and the value set is

convert_tz(updatedDate,'+05:30','-05:00') < '2013-04-14 09:30:00'

但它给了我不同的值，如下

but it gives me different values like this

product    count
-------    ------
    A        155
    B        19

所以，我怀疑是TimeZone问题我将代码更改为

So, I suspected that it is TimeZone problem I changed my code to

end.setTimeZone(TimeZone.getTimeZone("America/New York"));
pst.setTimestamp(1, new java.sql.Timestamp(end.getTimeInMillis()));

                rs=pst.executeQuery();
                System.out.println("=====new Open Tickets Query executed=====");
                System.out.println(pst);

但它仍然会给出相同的错误结果。

but it still gives same wrong result.

更多信息：我如何设置日历结束变量

我有一个Web应用程序，它给我日期字符串2013-04-14 09:30:00

I have a web application which gives me date string "2013-04-14 09:30:00"

            DateFormat df1=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                Calendar end=Calendar.getInstance();
                end.setTime(df1.parse(endString));
                end.set(Calendar.HOUR, 9);
                end.set(Calendar.MINUTE, 30);
                end.set(Calendar.SECOND, 0);

另外，对于实验，我尝试使用java.util.Date对象，它给出了正确的结果以下是代码

Also, for experiment I tried with java.util.Date object it gives me correct result following is the code

SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
end.setTime(sdf.parse("2012-10-01 00:00:00"));
pst.setTimestamp(1, new java.sql.Timestamp(end.getTime()));

更新： -
如果我使用弃用的方法答案是正确的

UPDATE :- If I use a deprecated method the answer is correct

 pst.setTimestamp(1, new java.sql.Timestamp(octDate.get(Calendar.YEAR)-1900,octDate.get(Calendar.MONTH),octDate.get(Calendar.DATE),octDate.get(Calendar.HOUR),octDate.get(Calendar.MINUTE),octDate.get(Calendar.SECOND),0));
pst.setTimestamp(2, new java.sql.Timestamp(end.get(Calendar.YEAR)-1900,end.get(Calendar.MONTH),end.get(Calendar.DATE),end.get(Calendar.HOUR),end.get(Calendar.MINUTE),end.get(Calendar.SECOND),0));

更新2： -
建议第一个回答后我这样做了

UPDATE 2:- After the suggestion of first answer I did this

1）在mysql中执行 SELECT NOW（）并返回' 2013-04-22 11:56:08'

1) executed SELECT NOW() in mysql and it returned '2013-04-22 11:56:08'

2）执行

System.out.println(new Date(System.currentTimeMillis()));

输出： Mon Apr 22 11:56:25 IST 2013

表示两个系统都有相同的时区

means both systems have same timezone

推荐答案

背景：即使是出色的程序员也有一个令人惊讶的常见和大错误概念，即存储时间戳（在您的数据库中，日期，日历，时间戳等）以某种方式具有时区信息。 他们没有。时间戳（直到Java 8，无论如何）被存储为自1970年1月1日午夜起的毫秒数。句末。设置时区的唯一方法是向计算机提供足够的信息以将该时间戳转换为人类可读的格式，反之亦然。

Background: A surprisingly common--and big--misconception shared by even brilliant programmers is the notion that stored time stamps (in your database, Date, Calendar, Timestamp, et al) somehow have time zone information. They do not. A time stamp (up until Java 8, anyway) is stored as the number of milliseconds since midnight on 1 Jan 1970 UTC. End of sentence. The only thing setting the time zone does is provide enough information to the computer to convert that time stamp to a human readable format, and vice versa.

答案：当您怀疑时这是一个时区问题，你是对的。但是您用来尝试验证此代码的代码也存在问题：

Answer: When you suspected that this was a time zone problem, you were right. But the code you used to try to verify this also has a problem:

end.setTimeZone(TimeZone.getTimeZone("America/New York"));
pst.setTimestamp(1, new java.sql.Timestamp(end.getTimeInMillis()));

setTimeZone 语句否效果存储在 end 中的时间，因为已经设置了时间。如果您之后存储了时间，那么它只会产生影响，然后只有当您使用Calendar的一种方法时，才能从人类可读的格式转换时间（而不是 setTimeInMillis ）。

That setTimeZone statement has no effect on the time stored in end, because the time has already been set. It would only have had an effect if you stored the time afterwards, and then only if you used one of Calendar's methods which converted the time from a human readable format (and not setTimeInMillis).

当你使用 getTimeInMillis 将时间戳传递给准备好的陈述时，你正在检索时间直接盖章。由于您没有将其转换为人格式，因此会再次忽略时区信息。

When you use getTimeInMillis to pass the time stamp to your prepared statement, you're retrieving the time stamp directly. Since you're not converting it to a human format, once again the time zone information is ignored.

当您尝试

SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
end.setTime(sdf.parse("2012-10-01 00:00:00"));
pst.setTimestamp(1, new java.sql.Timestamp(end.getTime()));

和

pst.setTimestamp(1, new java.sql.Timestamp(octDate.get(Calendar.YEAR)-1900,octDate.get(Calendar.MONTH),octDate.get(Calendar.DATE),octDate.get(Calendar.HOUR),octDate.get(Calendar.MINUTE),octDate.get(Calendar.SECOND),0));
pst.setTimestamp(2, new java.sql.Timestamp(end.get(Calendar.YEAR)-1900,end.get(Calendar.MONTH),end.get(Calendar.DATE),end.get(Calendar.HOUR),end.get(Calendar.MINUTE),end.get(Calendar.SECOND),0));

似乎工作正常，因为您现在正在使用转换为/的方法从人类可读格式，因此使用指定的时区信息。但是，这只是掩盖了真正的问题。真正的问题是，当您从 endString 解析时，时间被不正确地转换。也就是说，表达的 endString 的时区与 df1 中设置的时区不匹配解析了日期。

things appear to work because you are now using methods which convert to/from a human readable format, and therefore the specified time zone information is used. However, this is only covering up the real problem. The real issue is that the time was improperly converted when you parsed it from endString. That is, the time zone that endString was expressed in does not match the time zone set in df1 at the time the date was parsed.

简短的回答：在这一行之前：

SHORT ANSWER: before this line:

end.setTime(df1.parse(endString));

您需要：

• 计算出 endString 中的时间区域。


• 设置 df1 和不 结束到同一时区。由于 df1 是从人格式转换日期的东西，所以它是使用的时区信息。

• Figure out what time zone the time in endString was expressed in.
• Set df1 and not end to that same time zone. Since df1 is the thing that is converting the date from human format, it's that time zone information that's used.

干杯！

这篇关于TimeZone在mysql和java中的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！