存储“假”时间戳到数据库 [英] Storing a "fake" timestamp into a database

问题描述

这里是我要解决的问题：从数据库A读取一个字符串，将字符串转换为一个Date对象，将Date对象存储到数据库B。

EX）数据库A：从数据库A读入日期字符串2015-03-08 02:00:00，转换为Date对象，存回数据库B中。

这里出现的问题是因为2:00 AM是美国中部时间的DST的开始，因此Data对象将2:00 AM直接转换为3:00 AM，这意味着3:00 AM被存储到数据库B

。

有什么办法纠正这个问题吗？如果有必要，我不反对使用Joda Time。

我想集中于上述日期，2015-03-08 02:00:00

这是我使用的代码：

  SimpleDateFormat sdf = new SimpleDateFormat（yyyy-MM- dd HH：mm：ss.S）; 
 sdf.setTimeZone（TimeZone.getTimeZone（UTC））; 
 String date =2015-03-08 02：00：00.0; 
 
 
 
 try 
 {
 d = sdf.parse（date）; 
 sdf.format（d）; 
 
 //插入数据库
 // --- 
 // 
} 
 catch（ParseException e）
 {
 // TODO自动生成的catch块
 e.printStackTrace（）; 
} 
  

解决方案

您不应该从数据库中读取字符串的日期时间值，您应该阅读日期时间对象。在StackOverflow上有关于从数据库读取/写入日期时间值的许多问题，因此无需在此重复。

有一个字符串，如2015-03-08 02:00:00，注意缺少时区或偏移量的任何指标。如果您想假设该字符串表示特定于美国中部时间的时间，那么您必须接受这样的事实，即没有这样的日期时间，因为夏令时（DST）将其定义为凌晨3点。在2 AM的行程，时间标记跳到2 AM。

使用适当的时区名称

日期时间工作的大提示：避免将时区视为中央时间，并使用3-4字母代码，如CST。这些不是标准化的，也不是唯一的（许多重复），并进一步混淆夏令时的混乱。在continent / majorCityOrRegion模式中使用正确的时区。

本地日期时间

也许你的意思是我们所说的本地时间时间不是特定于任何一个时区。例如，圣诞节从2015年12月25日午夜开始。这意味着每个特定时区的不同时刻。

Joda-Time

让我们将这个字符串解释为Joda-Time中的 LocalDateTime 。首先，为了方便起见，我们将SPACE替换为T，以利用Joda-Time的内置解析器 ISO 8601 格式。

  String input =2015-03-08 02:00:00; 
 String inputStandardized = input.replace（，T）; //为方便起见，将输入文本转换为符合ISO 8601标准的规范格式。替换日期&时间部分具有T。 
  

接下来我们解析标准化字符串。

  LocalDateTime localDateTime = LocalDateTime.parse（inputStandardized）; 
  

转储到控制台。

  System.out.println（inputStandardized：+ inputStandardized）; 
 System.out.println（localDateTime：+ localDateTime）; 
  

运行时。

  inputStandardized：2015-03-08T02：00：00 
 localDateTime：2015-03-08T02：00：00.000 
  
 
 
 
此本地日期时间可以使用 SQL类型 无时区的TIMESTAMP 。此类型表示在获取（SELECT）或放置（INSERT / UPDATE）数据库值时不对UTC时区进行调整。有关这些SQL类型的详细信息，请参见 Postgres doc 。 p> 
 
 
分区日期时间
 
 
 
如果您要表示特定时区的特定时刻，例如 America / Chicago ，当我们需要分配该时区时。对于这种特定于时区的值，在数据库中，您将使用数据类型 TIMESTAMP WITH TIME ZONE 。该类型名称具有误导性 - 意味着对于时区，因为它会将输入数据调整为UTC。数据的原始时区将丢失。
 
 
 
不幸的是，这是Joda-Time让我们失望的少数情况之一。而不是做一个调整，Joda-Time拒绝，抛出异常。 ☹
 
 
 
查看自己...让我们在上面的示例代码中添加以下代码。
 code> DateTimeZone zone = DateTimeZone.forID（America / Chicago）; 
 DateTime dateTimeChicago = localDateTime.toDateTime（zone）; //如果输入缺少偏移，则Joda-Time *将*指定为指定时区的值。如果输入有偏移，Joda-Time *调整*到指定区域的值。 
  
转储到控制台。
  System.out.println（zone：+ zone）; 
 System.out.println（dateTime：+ dateTimeChicago）; 
  
运行时。
 线程main中的异常org.joda.time.IllegalInstantException：由于时区偏移转换（夏令时间gap）导致的非法即时：2015-03-08T02： 00：00.000（美国/芝加哥
 ... 
  
似乎没有很好的广义解决方法，基本上，如果你期望一个特定的时区，你自己做调整。看看像此，此，此，以及 Joda -Time FAQ 。
 
 
 
 java.time 
 
 
 
在Java 8及更高版本中，  java中的新内置日期 - 时间框架.time包（教程）。这个框架的灵感来自Joda-Time，并且比Joda-Time有一些优势。其中一个优点是处理这个DST不存在的值问题。
  String input =2015-03-08 02： 00:00; 
 String inputStandardized = input.replace（，T）; 
 
 LocalDateTime localDateTime = LocalDateTime.parse（inputStandardized）; 
  
让我们调整本地日期时间以分配特定时区。 java.time框架检测不存在的日期时间，并自动将日期时间推迟到尊重DST转换。
  ZoneId zone = ZoneId.of（America / Chicago）; 
 ZonedDateTime zdt = ZonedDateTime.of（localDateTime，zone）; 
  
转储到控制台。
  System.out.println（inputStandardized：+ inputStandardized）; 
 System.out.println（localDateTime：+ localDateTime）; 
 System.out.println（zone：+ zone）; $ b¥b System.out.println（zdt：+ zdt）; 
  
运行时。
  inputStandardized：2015-03-08T02：00：00 
 localDateTime：2015-03-08T02：00 
 zone：America / Chicago 
 zdt：2015-03-08T03：00-05：00 [美洲/芝加哥] 
  
 
 
 
 SQL < h1> 
 
 
如上所述，您可以在StackOveflow中搜索获取日期时间进出数据库的详细信息。 
 
 
 
理想情况下，使用java.time，您可以直接输入  LocalDateTime  或  ZonedDateTime  到您的JDBC驱动程序。但是大多数驱动程序还没有更新来处理java.time类型。在您的驱动程序更新之前，请返回 java .sql。*类。方便的转换方法可以在与Java捆绑的新旧类上找到。 
  java.sql.Timestamp ts = java.sql.Timestamp.valueOf（localDateTime）; 
  
 ...或... 
  Instant instant = zdt.toInstant（）; 
 java.sql.Timestamp ts = java.sql.Timestamp.from（instant）; 
  
 
Here is the problem I am trying to solve: Read a string from database A, convert the string into a Date object, store the Date object into database B.

EX) Database A: Read in date string "2015-03-08 02:00:00" from database A, convert into a Date object, store back into database B.

The problem here occurs because 2:00 AM is the beginning of DST in U.S. Central time, so the Data object converts 2:00 AM straight into 3:00 AM, which means 3:00 AM gets stored into database B.

Is there any way to correct this? I am not opposed to using Joda Time if necessary. 

I am trying to focus on the above date, 2015-03-08 02:00:00

This is the code I am using:
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
    sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
    String date = "2015-03-08 02:00:00.0";



    try 
    {
        d = sdf.parse(date);
        sdf.format(d);

        //Insert into database here
        // ---
        //
    } 
    catch (ParseException e) 
    {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

解决方案
You have multiple issues intertwined.

You should not be reading strings from a database for date-time values, you should be reading date-time objects. There are many Questions on StackOverflow about reading/writing date-time values from/to databases, so no need to repeat here.

If you do have a string, such as "2015-03-08 02:00:00", notice the lack of any indicator of a time zone or offset. If you want to assume that string represents a time specific the US Central Time, then you must accept the fact that there is no such date-time as that because Daylight Saving Time (DST) defines that as 3 AM. At the stroke of 2 AM, the time labeling jumps to 2 AM. So there is no point in trying to get such a non-existent date-time.

Use Proper Time Zone Names

Big tip for date-time work: Avoid thinking about time zones as "Central Time" and the 3-4 letter codes like "CST". These are not standardized, nor are the unique (many duplicates), and further confuse the mess that is Daylight Saving Time. Use a proper time zone, in pattern of "continent/majorCityOrRegion". 

Local Date-Time

Perhaps what you mean is what we call "local time" where the date-time is not specific to any one time zone. For example, "Christmas starts at midnight on December 25th 2015". That means a different moment in each particular time zone. Christmas dawns earlier in Paris, than Montréal, for example.

Joda-Time

Let's interpret that string as a LocalDateTime in Joda-Time. First, for convenience, we replace the SPACE with a "T" to take advantage of Joda-Time’s built-in parsers for ISO 8601 formats.
String input = "2015-03-08 02:00:00";
String inputStandardized = input.replace( " ", "T" );  // For convenience, convert input text to comply with ISO 8601 standard’s canonical format. Replace SPACE between date & time portions with "T".
Next we parse that standardized string.
LocalDateTime localDateTime = LocalDateTime.parse( inputStandardized );
Dump to console.
System.out.println( "inputStandardized: " + inputStandardized );
System.out.println( "localDateTime: " + localDateTime );
When run.
inputStandardized: 2015-03-08T02:00:00
localDateTime: 2015-03-08T02:00:00.000
This local date-time could be stored in a SQL database using the SQL type TIMESTAMP WITHOUT TIME ZONE. This type means no adjustments to UTC time zone are to be made in either getting (SELECT) or putting (INSERT / UPDATE) database values. See Postgres doc for more info on these SQL types.

Zoned Date-Time

If you meant to represent the specific moment in a specific time zone such as America/Chicago, when we need to assign that time zone. For this kind of time-zone-specific values, in your database you would use the data type TIMESTAMP WITH TIME ZONE. That type name is misleading -- it means with respect for time zone, as it adjusts incoming data to UTC. The data's original time zone is then lost.

Unfortunately, this is one of the few situations where Joda-Time lets us down. Rather than do an adjustment, Joda-Time refuses, throwing an exception. ☹

See for yourself… Let's add the following code to the example code above.
DateTimeZone zone = DateTimeZone.forID( "America/Chicago" );
DateTime dateTimeChicago = localDateTime.toDateTime( zone ); // If the input lacks an offset, then Joda-Time *assigns* the value the specified time zone. If the input has an offset, Joda-Time *adjusts* the value to the specified zone.
Dump to console.
System.out.println( "zone: " + zone );
System.out.println( "dateTime: " + dateTimeChicago );
When run.
Exception in thread "main" org.joda.time.IllegalInstantException: Illegal instant due to time zone offset transition (daylight savings time 'gap'): 2015-03-08T02:00:00.000 (America/Chicago
…
There appears to be no good generalized workaround, just hacks. Basically, if you expect a certain time zone, you make the adjustment yourself. See discussions like this, this, this, and the Joda-Time FAQ.

java.time

In Java 8 and later, we have the new built-in date-time framework in the java.time package (Tutorial). This framework was inspired by Joda-Time, and has some advantages over Joda-Time. One of those advantages is handling of this DST non-existent value problem.
String input = "2015-03-08 02:00:00";
String inputStandardized = input.replace( " ", "T" );  

LocalDateTime localDateTime = LocalDateTime.parse( inputStandardized );
Let's adjust that local date-time to assign a specific time zone. The java.time framework detects the non-existent date-time and automatically slides the time-of-day forward to respect the DST transition.
ZoneId zone = ZoneId.of( "America/Chicago" );
ZonedDateTime zdt = ZonedDateTime.of( localDateTime, zone );
Dump to console.
System.out.println("inputStandardized: " + inputStandardized );
System.out.println("localDateTime: " + localDateTime );
System.out.println("zone: " + zone );
System.out.println("zdt: " + zdt );
When run.
inputStandardized: 2015-03-08T02:00:00
localDateTime: 2015-03-08T02:00
zone: America/Chicago
zdt: 2015-03-08T03:00-05:00[America/Chicago]


SQL

As said above, you can search StackOveflow for much info on getting date-times in and out of databases. 

Ideally, with java.time, you could directly feed either the LocalDateTime or ZonedDateTime to your JDBC driver. But most drivers have not yet be updated to handle the java.time types. Until your driver is updated, fall back on the java.sql.* classes. Convenient conversion methods can be found on both the new and old classes bundled with Java. 
java.sql.Timestamp ts = java.sql.Timestamp.valueOf( localDateTime );
…or…
Instant instant = zdt.toInstant();
java.sql.Timestamp ts = java.sql.Timestamp.from( instant );


                        
这篇关于存储“假”时间戳到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！