java.sql.Timestamp 时区是特定的吗? [英] Is java.sql.Timestamp timezone specific?

问题描述

我必须将 UTC 日期时间存储在数据库中.
我已将特定时区中给出的日期时间转换为 UTC.为此，我遵循了以下代码.
我输入的日期时间是20121225 10:00:00 Z"时区是亚洲/加尔各答"
我的服务器/数据库(oracle)运行在同一时区(IST)亚洲/加尔各答"

获取此特定时区中的日期对象

 String date = "20121225 10:00:00 Z";String timeZoneId = "亚洲/加尔各答";TimeZone timeZone = TimeZone.getTimeZone(timeZoneId);DateFormat dateFormatLocal = new SimpleDateFormat("yyyyMMdd HH:mm:ss z");//这个日期对象被赋予了时间和时区java.util.Date parsedDate = dateFormatLocal.parse(date + " "+ timeZone.getDisplayName(false, TimeZone.SHORT));if (timeZone.inDaylightTime(parsedDate)) {//我们需要重新解析，因为我们不知道日期//是 DST 直到它被解析...parsedDate = dateFormatLocal.parse(date + " "+ timeZone.getDisplayName(true, TimeZone.SHORT));}//赋值给java.sql.TimeStamp实例变量obj.setTsSchedStartTime(new java.sql.Timestamp(parsedDate.getTime()));

存储到数据库中

 if (tsSchedStartTime != null) {stmt.setTimestamp(11, tsSchedStartTime);} 别的 {stmt.setNull(11, java.sql.Types.DATE);}

输出

DB (oracle) 存储了相同的给定 dateTime: "20121225 10:00:00 而不是 UTC.

我已经从下面的 sql 中确认了.

 从 myTable 中选择 to_char(sched_start_time, 'yyyy/mm/dd hh24:mi:ss')

我的数据库服务器也运行在同一时区亚洲/加尔各答"

它给了我以下外观

1. Date.getTime() 不是 UTC
2. 或者时间戳在存储到数据库时会受到时区影响我在这里做错了什么?

还有一个问题:

timeStamp.toString() 会像 java.util.date 一样在本地时区打印吗?不是UTC?

解决方案

虽然没有明确指定 setTimestamp(int parameterIndex, Timestamp x) 驱动程序必须遵循 setTimestamp(int parameterIndex, Timestamp x, Calendar cal) javadoc:

<块引用>

使用给定的 Calendar 对象将指定的参数设置为给定的 java.sql.Timestamp 值.驱动程序使用 Calendar 对象构造一个 SQL TIMESTAMP 值，然后驱动程序将其发送到数据库.使用 Calendar 对象，驱动程序可以在考虑自定义时区的情况下计算时间戳.如果未指定 Calendar 对象，则驱动程序使用默认时区，即运行应用程序的虚拟机的时区.

当您使用 setTimestamp(int parameterIndex, Timestamp x) 调用时，JDBC 驱动程序使用虚拟机的时区来计算该时区中时间戳的日期和时间.此日期和时间是存储在数据库中的内容，如果数据库列不存储时区信息，则有关该时区的任何信息都将丢失(这意味着使用数据库的应用程序可以使用始终使用相同的时区或想出其他方案来识别时区(即存储在单独的列中).

例如:您当地的时区是 GMT+2.您存储2012-12-25 10:00:00 UTC".数据库中存储的实际值为2012-12-25 12:00:00".您再次检索它:您将其再次检索为2012-12-25 10:00:00 UTC"(但前提是您使用 getTimestamp(..) 检索它)，但是当另一个应用程序在 GMT+0 时区访问数据库，它将检索时间戳为2012-12-25 12:00:00 UTC".

如果您想将其存储在不同的时区，那么您需要使用 setTimestamp(int parameterIndex, Timestamp x, Calendar cal) 与所需时区中的 Calendar 实例.只需确保在检索值时也使用具有相同时区的等效 getter(如果您在数据库中使用没有时区信息的 TIMESTAMP).

因此，假设您要存储实际的 GMT 时区，则需要使用:

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));stmt.setTimestamp(11, tsSchedStartTime, cal);

对于 JDBC 4.2，一个兼容的驱动程序应该支持 java.time.LocalDateTime(和 java.time.LocalTime)的 TIMESTAMP(和 java.time.LocalTime)code>TIME) 到 get/set/updateObject.java.time.Local* 类没有时区，因此不需要应用转换(尽管如果您的代码确实假定了特定时区，这可能会引发一系列新问题).>

I have to store UTC dateTime in DB.
I have converted the dateTime given in specific timezone to UTC. for that I followed the below code.
My input dateTime is "20121225 10:00:00 Z" timezone is "Asia/Calcutta"
My Server/DB(oracle) is running in the same timezone(IST) "Asia/Calcutta"

Get the Date object in this specific Timezone

        String date = "20121225 10:00:00 Z";
        String timeZoneId = "Asia/Calcutta";
        TimeZone timeZone = TimeZone.getTimeZone(timeZoneId);

        DateFormat dateFormatLocal = new SimpleDateFormat("yyyyMMdd HH:mm:ss z");
                    //This date object is given time and given timezone
        java.util.Date parsedDate = dateFormatLocal.parse(date + " "  
                         + timeZone.getDisplayName(false, TimeZone.SHORT));

        if (timeZone.inDaylightTime(parsedDate)) {
            // We need to re-parse because we don't know if the date
            // is DST until it is parsed...
            parsedDate = dateFormatLocal.parse(date + " "
                    + timeZone.getDisplayName(true, TimeZone.SHORT));
        }

       //assigning to the java.sql.TimeStamp instace variable
        obj.setTsSchedStartTime(new java.sql.Timestamp(parsedDate.getTime()));

Store into DB

        if (tsSchedStartTime != null) {
            stmt.setTimestamp(11, tsSchedStartTime);
        } else {
            stmt.setNull(11, java.sql.Types.DATE);
        }

OUTPUT

DB (oracle) has stored the same given dateTime: "20121225 10:00:00 not in UTC.

I have confirmed from the below sql.

     select to_char(sched_start_time, 'yyyy/mm/dd hh24:mi:ss') from myTable

My DB server also running on the same timezone "Asia/Calcutta"

It gives me the below appearances

1. Date.getTime() is not in UTC
2. Or Timestamp is has timezone impact while storing into DB What am I doing wrong here?

One more question:

Will timeStamp.toString() print in local timezone like java.util.date does? Not UTC?

解决方案

Although it is not explicitly specified for setTimestamp(int parameterIndex, Timestamp x) drivers have to follow the rules established by the setTimestamp(int parameterIndex, Timestamp x, Calendar cal) javadoc:

Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object. The driver uses the Calendar object to construct an SQL TIMESTAMP value, which the driver then sends to the database. With a Calendar object, the driver can calculate the timestamp taking into account a custom time zone. If no Calendar object is specified, the driver uses the default time zone, which is that of the virtual machine running the application.

When you call with setTimestamp(int parameterIndex, Timestamp x) the JDBC driver uses the time zone of the virtual machine to calculate the date and time of the timestamp in that time zone. This date and time is what is stored in the database, and if the database column does not store time zone information, then any information about the zone is lost (which means it is up to the application(s) using the database to use the same time zone consistently or come up with another scheme to discern timezone (ie store in a separate column).

For example: Your local time zone is GMT+2. You store "2012-12-25 10:00:00 UTC". The actual value stored in the database is "2012-12-25 12:00:00". You retrieve it again: you get it back again as "2012-12-25 10:00:00 UTC" (but only if you retrieve it using getTimestamp(..)), but when another application accesses the database in time zone GMT+0, it will retrieve the timestamp as "2012-12-25 12:00:00 UTC".

If you want to store it in a different timezone, then you need to use the setTimestamp(int parameterIndex, Timestamp x, Calendar cal) with a Calendar instance in the required timezone. Just make sure you also use the equivalent getter with the same time zone when retrieving values (if you use a TIMESTAMP without timezone information in your database).

So, assuming you want to store the actual GMT timezone, you need to use:

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
stmt.setTimestamp(11, tsSchedStartTime, cal);

With JDBC 4.2 a compliant driver should support java.time.LocalDateTime (and java.time.LocalTime) for TIMESTAMP (and TIME) through get/set/updateObject. The java.time.Local* classes are without time zones, so no conversion needs to be applied (although that might open a new set of problems if your code did assume a specific time zone).

这篇关于java.sql.Timestamp 时区是特定的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！