JDBC + MySQL可以被说服忽略时区吗？ [英] Can JDBC + MySQL be convinced to ignore timezones?

问题描述

我有一个Java Web应用程序，其中客户端将带时间戳的数据发送到服务器以存储在MySQL数据库中并在以后检索。这些时间戳应始终视为本地时间，并且永远不会根据时区进行调整：如果一个时区中的客户端输入的时间为18:00:00，则应在另一个时区显示为18:00:00的客户端。

I've got a Java web application in which the client sends timestamped data to the server to be stored in a MySQL database and retrieved later. These timestamps should always be treated as local time and never adjusted for timezone: If a client in one timezone inputs a time of 18:00:00, it should display for a client in another timezone as 18:00:00.

保存此值的数据库列的类型为DATETIME，因为我认为它没有时区调整。 java应用程序接收时间戳作为java.util.Date对象，并在将它们插入PreparedStatements之前将它们转换为java.sql.Timestamp对象。然而，在某个地方，当客户端和服务器位于不同的时区时，时间值会被抵消。

The database column holding this value is of type DATETIME, as I was under the impression that it does no timezone-adjusting. The java application receives timestamps as java.util.Date objects, and converts them to java.sql.Timestamp objects before inserting them in PreparedStatements. Somewhere along the way however, the time values are getting offset when the client and server are in different time zones.

为了让您知道我在寻找什么，我可以将所有时间戳存储为字符串，这正是我想要的效果，但是我必须将字符串格式化并解析为需要操作的日期。

To give you an idea of what I'm looking for, I could store all my timestamps as strings and that would be exactly the effect I want, but it doesn't make sense to me to have to format and parse strings as dates when they need to be operated on.

当时间戳值始终被视为本地时间时，处理此类应用程序的常用方法是什么？

What is the usual way to handle such applications when timestamp values should always be treated as local time?

推荐答案

麻烦的是 java.util.Date 表示时间点不是像18:00那样的时间。 （在这种情况下，时间点是从格林威治标准时间1970年1月1日00:00:00以来的毫秒数来衡量的，但原则上这里无关紧要。）

The trouble is that java.util.Date represents a "point in time" not a time like 18:00. (Point in time, in this case, is measured by the number of milliseconds since 1st Jan 1970 00:00:00 GMT but in principle that doesn't matter here.)

通常的方式（至少，我一直这样做的方式）是在数据库中存储时间点，所以如果你在下午1点做某事，我会把它看作例如下午3点，因为我的时区是同一个时间点。因此，存储时间（HH：MM）而不存储时间点的要求，即不调整收件人的时区，这是非常不寻常的，至少根据我的经验。

The usual way (at least, the way I've always done it) is to store points in time in the database, so if you do something at 1pm your time, I see it as e.g. 3pm as that's the same point in time in my timezone. So the requirement to store a "time" (HH:MM) without storing a "point in time", i.e. without adjusting for the recipient's timezone, is quite unusual, at least in my experience.

我会在你使用时使用 DATETIME 数据类型，但是插入并从Java读取值作为字符串，即 上的 PreparedStatement 和 getString 上的setString 方法 ResultSet 。

I would use the DATETIME datatype as you're using, but insert and read the values as strings from Java i.e. with the setString method on the PreparedStatement and getString on the ResultSet.

这篇关于JDBC + MySQL可以被说服忽略时区吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！