Java JDBC 时间戳和日期 GMT 问题

JDBC Timestamp & Date GMT issues

提问by n002213f

I have a JDBC Date column, which if a i use getDate is get the 'date' part only 02 Oct 2009but if i use getTimestamp i get the full 'date' 02 Oct 2009 13:56:78:890. This is excatly what i want.

我有一个 JDBC 日期列，如果 ai 使用 getDate 是仅在2009 年 10 月 2 日获得“日期”部分，但如果我使用 getTimestamp，我将获得完整的“日期” 2009 年 10 月 2 日 13:56:78:890。这正是我想要的。

However the 'date' returned by getTimestamp 'ignores' the GMT values, suppose date; 02 Oct 2009 13:56:78:890, i end up getting 02 Oct 2009 15:56:78:890

然而，getTimestamp 返回的“日期”会“忽略”格林威治标准时间值，假设日期；02 Oct 2009 13:56:78:890，我最终得到02 Oct 2009 15:56:78:890

My date was saved as a +2GMT date on the database but the application server is on GMT i.e 2hrs behind

我的日期在数据库中保存为 +2GMT 日期，但应用程序服务器在 GMT 上，即晚了 2 小时

How can still get my date as is, 02 Oct 2009 13:56:78:890

2009 年 10 月 2 日 13:56:78:890怎么还能按原样得到我的日期

Edit

编辑

I get the date +2 on the client side that is on GMT +2

我在 GMT +2 的客户端得到日期 +2

采纳答案by ZZ Coder

That's the difference between Timestamp and other temporal types in MySQL. Timestamp is saved as Unix time_t in UTC but other types store date/time literally without zone information.

这就是 Timestamp 和 MySQL 中其他时间类型的区别。时间戳在 UTC 中保存为 Unix time_t，但其他类型在没有区域信息的情况下逐字存储日期/时间。

When you call getTimestamp(), MySQL JDBC driver converts the time from GMT into default timezone if the type is timestamp. It performs no such conversion for other types.

当您调用 getTimestamp() 时，如果类型为时间戳，MySQL JDBC 驱动程序会将时间从 GMT 转换为默认时区。它不会对其他类型执行此类转换。

You can either change the column type or do the conversion yourself. I recommend former approach.

您可以更改列类型或自己进行转换。我推荐以前的方法。

回答by Jesper

You should be aware that java.util.Date(and also java.sql.Dateand java.sql.Timestamp, which are subclasses of java.util.Date) don't know anything about timezones, or rather, they are always in UTC.

您应该知道java.util.Date（以及java.sql.Date和java.sql.Timestamp，它们是 的子类java.util.Date）对时区一无所知，或者更确切地说，它们始终使用 UTC。

java.util.Dateand its subclasses are nothing more than a container for a "number of milliseconds since 01-01-1970, 12:00 AM UTC" value.

java.util.Date它的子类只不过是“自 01-01-1970, 12:00 AM UTC 以来的毫秒数”值的容器。

To display a date in a specific timezone, convert it to a string by using a java.text.DateFormatobject. Set the timezone on that object by calling the setTimeZone()method. For example:

要在特定时区显示日期，请使用java.text.DateFormat对象将其转换为字符串。通过调用该setTimeZone()方法在该对象上设置时区。例如：

Date date = ...;  // wherever you get this from

DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

// Make the date format show the date in CET (central European time)
df.setTimeZone(TimeZone.getTimeZone("CET"));

String text = df.format(date);

回答by n002213f

From this posti have come to the conclusion that JDBC does retrieve the timezone for the Timestamp (I don't think MS SQL support this as well, most Google results point to Oracle)

从这篇文章我得出的结论是 JDBC 确实检索时间戳的时区（我认为 MS SQL 不支持这一点，大多数 Google 结果都指向 Oracle）

When the JDBC getTimeStamp method is called it only gets the 'milliseconds'portion and create a Date object with the server TimeZone, which is GMT.

当调用 JDBC getTimeStamp 方法时，它只获取“毫秒”部分并使用服务器 TimeZone（格林威治标准时间）创建一个 Date 对象。

When this Date object is presented to my client which is +2 GMT, it adds 2 hours which is the standard offset which leads to the extra hours.

当这个 Date 对象呈现给我的客户时，它是 +2 GMT，它增加了 2 小时，这是导致额外小时的标准偏移量。

I have corrected this by removing the time offset from the date i retrieve i.e. convert to the true GMT Date.

我通过从我检索的日期中删除时间偏移来纠正这个问题，即转换为真正的 GMT 日期。

回答by Jim P

I ran into a similar problem the other day where the time component was being truncated from some dates.

前几天我遇到了类似的问题，其中时间部分从某些日期被截断。

We narrowed it down to a difference in Oracle Driver versions.

我们将其缩小到 Oracle 驱动程序版本的差异。

On Oracle's FAQ there is a section about this:

在 Oracle 的 FAQ 上有一个关于这个的部分：

---

---

select sysdate from dual; ...while(rs.next())

Prior to 9201, this will return: getObject for sysdate : java.sql.Timestamp <<<< getDate for sysdate : java.sql.Date getTimetamp for sysdate : java.sql.Timestamp

在 9201 之前，这将返回： getObject for sysdate : java.sql.Timestamp <<<< getDate for sysdate : java.sql.Date getTimetamp for sysdate : java.sql.Timestamp

As of 9201 onward the following will be returned

从 9201 开始，将返回以下内容

getObject for sysdate : java.sql.Date <<<<< getDate for sysdate :java.sql.Date >> no change getTimetamp for sysdate :java.sql.Timestamp >> no change

getObject for sysdate : java.sql.Date <<<<< getDate for sysdate :java.sql.Date >> 无变化 getTimetamp for sysdate :java.sql.Timestamp >> 无变化

Note: java.sql.Date has no time portion whereas java.sql.Timestamp does.

注意：java.sql.Date 没有时间部分，而 java.sql.Timestamp 有。

With this change in Datatype mapping, some application will fail and/or generate incorrect results when JDBC driver is upgraded from 8i/ 9iR1 to 920x JBDC driver. To maintain compatibility and keep applications working after upgrade, a compatibility flag was Provided. Developers now have some options:

随着数据类型映射的这一变化，当 JDBC 驱动程序从 8i/9iR1 升级到 920x JBDC 驱动程序时，某些应用程序将失败和/或生成不正确的结果。为了保持兼容性并在升级后保持应用程序正常运行，提供了兼容性标志。开发人员现在有一些选择：

1. Use oracle.jdbc.V8Compatible flag.

1. 使用 oracle.jdbc.V8Compatible 标志。

JDBC Driver does not detect database version by default. To change the compatibility flag for handling TIMESTAMP datatypes, connection property

JDBC Driver 默认不检测数据库版本。要更改处理 TIMESTAMP 数据类型的兼容性标志，连接属性

'oracle.jdbc.V8Compatible'

'oracle.jdbc.V8Compatible'

can be set to 'true' and the driver behaves as it behaved in 8i, 901x, 9 200 (with respect to TIMESTAMPs).

可以设置为“true”，驱动程序的行为就像它在 8i、901x、9 200 中的行为（关于 TIMESTAMP）。

By default the flag is set to 'false'. In OracleConnection constructor the driver obtains the server version and set the compatibility flag appropriately.

默认情况下，该标志设置为“false”。在 OracleConnection 构造函数中，驱动程序获取服务器版本并适当地设置兼容性标志。

java.util.Properties prop=newjava.util.Properties(); 
prop.put("oracle.jdbc.V8Compatible","true"); 
prop.put("user","scott"); 
prop.put("password","tiger");
String url="jdbc:oracle:thin:@host:port:sid"; 
Connection conn = DriverManager.getConnection(url,prop);

With JDBC 10.1.0.x, instead of the connection property, the following system property can be used: java -Doracle.jdbc.V8Compatible=true.....Note: This flag is a client only flag that governs the Timestamp and Date mapping. It does not affect any Database feature.

对于 JDBC 10.1.0.x，可以使用以下系统属性代替连接属性：java -Doracle.jdbc.V8Compatible=true.....注意：此标志是仅用于管理时间戳和的客户端标志日期映射。它不会影响任何数据库功能。

'2. Use set/getDate and set/getTimestamp when dealing with Date and TimeStamp column data type accordingly.

'2。相应地处理日期和时间戳列数据类型时，请使用 set/getDate 和 set/getTimestamp。

9i server supports both Date and Timestamp column types DATE is mapped to java.sql.Date and TIMESTAMP is mapped to java.sql.Timestamp.

9i 服务器支持日期和时间戳列类型 DATE 映射到 java.sql.Date 和 TIMESTAMP 映射到 java.sql.Timestamp。

---

---

So for my situation, I had code like this:

所以对于我的情况，我有这样的代码：

import java.util.Date; 
Date d = rs.getDate(1);

With 9i I was getting a java.sql.Timestamp (which is a subclass of java.util.Date) so everything was groovy and I had my hours and minutes.

使用 9i，我得到了一个 java.sql.Timestamp（它是 java.util.Date 的子类），所以一切都很好，我有我的时间和分钟。

But with 10g, the same code now gets a java.sql.Date (also a subclass of java.util.Date so it still compiles) but the HH:MM is TRUNCATED!!.

但是对于 10g，相同的代码现在得到一个 java.sql.Date（也是 java.util.Date 的子类，所以它仍然可以编译）但是 HH:MM 被截断了！！。

The 2nd solution was pretty easy for me - just replace the getDate with getTimestamp and you should be OK. I guess that was a bad habit.

第二个解决方案对我来说非常简单 - 只需将 getDate 替换为 getTimestamp 就可以了。我想这是一个坏习惯。

回答by Jayram

private Date convertDate(Date date1) throws ParseException {
        SimpleDateFormat sdfFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dateStr = sdfFormatter.format(date1);
        SimpleDateFormat sdfParser = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        sdfParser.setTimeZone(TimeZone.getTimeZone("GMT"));
        return sdfParser.parse(dateStr);
    }