java.sql.Timestamp时区是否具体？ [英] Is java.sql.Timestamp timezone specific?

问题描述

我必须将UTC dateTime存储在DB中。

我将特定时区中给出的dateTime转换为UTC。因为我遵循以下代码。

我的输入dateTime是20121225 10:00:00 Z时区是亚洲/加尔各答

我的服务器/数据库（oracle）是运行在相同的时区（IST）亚洲/加尔各答

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"

获取此特定时区中的Date对象

        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()));

存储到DB

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

OUTPUT

DB（oracle）已存储相同的给定 dateTime：20121225 10:00:00 不是UTC。

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

我从下面的sql中确认了。

I have confirmed from the below sql.

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

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

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

它给了我以下出现

1. Date.getTime（）不在UTC


2. 或者Timestamp在存储到DB
   时有时区影响？我在这里做错什么？ / li>
   

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

还有一个问题：

code> timeStamp.toString（）在本地时区打印，如 java.util.date 是否不是UTC？

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

推荐答案

虽然没有明确指定 setTimestamp（int parameterIndex ，Timestamp x）驱动程序必须遵循由 setTimestamp（int parameterIndex，Timestamp x，Calendar cal） javadoc ：

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:

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

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.

当您使用 setTimestamp（int parameterIndex，Timestamp x）调用时，JDBC驱动程序使用虚拟机的时区来计算该时区中的时间戳的日期和时间。该日期和时间是数据库中存储的数据库，如果数据库列不存储时区信息，则有关该区域的任何信息都将丢失（这意味着由应用程序使用数据库使用相同的时区一致或想出另一种计算时区（即存储在另一列中）的方案。

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).

例如：您当地的时区为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。

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".

如果要将其存储在不同的时区，那么你需要在必需的时区中使用 setTimestamp（int parameterIndex，Timestamp x，Calendar cal）与日历实例e。只需确保在检索值时使用相同时区的等效吸气剂（如果您在数据库中使用 TIMESTAMP 而没有时区信息）。

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).

所以，假设你想存储实际的GMT时区，你需要使用：

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);

使用JDBC 4.2，兼容驱动程序应支持 java.time.LocalDateTime （和 java.time.LocalTime ）for TIMESTAMP （和 TIME ）通过 get / set / updateObject 。 java.time.Local * 类没有时区，因此不需要应用转换（尽管如果您的代码确实具有特定的要求，那么这可能会打开一组新问题时区）。

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屋！