JPA TemporalType.Date gives an invalid date

Question

I have a class that has a date field representing the date "from date" for a piece of data. It is defined as follows:

@Temporal( TemporalType.DATE ) private Date validFrom; 

Everything seems to work fine right up until the moment when I pull the date out of the database and display it. If I select the date September 18, 2003 in the front end, then save it when I check in the database that it is pretty sure that it is a date (MySQL database 5.5.9 is of type DATE). However, when I pull out the list of records, the indicated date - September 17, 2003 - is one day earlier.

If I choose a date sooner or later in the year, for example, March 26, 2003 or December 25, 2003, everything is in order, so I assume that this is due to summer savings, but where is the mistake when it creeps? Since the database seems to be holding the correct date, I assume it should be when the JPA converts back to java.util.Date - is java.util.Date the best class to use for date? I saw a few examples where people use Calendar, but it looks pretty heavy, and I'm not sure how well it will work with a JSF based interface.

Answer 1

After much experimentation and searching, I'm sure I found the cause of the problem. The date is held in java.util.Date, which comes with all the time baggage and time zone. It would seem that the JPA reads the date September 18, 2003 from the database, and then fills in the following date: "Thu Sep 18 00:00:00 BST 2003" - note that the time zone was set to BST, probably because it was not explicitly specified by the database. Anyway, you need to format the output on the JSF page if you want to see only the date:

 <h:outputText value="#{t.validFrom}"> <f:convertDateTime pattern="dd MMM yyyy"/> </h:outputText> 

This, however, suggests that the time zone is what is currently operating on the machine. In my case, the time zone is currently GMT (because it is in winter), so when it is presented with the date “Thu Sep 18 00:00:00 BST 2003”, it will convert it to GMT by subtracting one hour, leaving the display at 17 September 2003

Answer 2

It is unfortunate, but all the answers so far, as a rule, are incorrect. The answer is quite simple, but it requires five points:

• DATE = java.sql.Date, which is a wrapper around java.util.Date, which is the number of milliseconds since the Age in the UTC time zone. So it has year / month / date / hours / minutes / seconds in a fixed time zone of GMT + 0 (UTC). Note, however, that java.sql.Date sets the time components to zero!
• TIMESTAMP = java.sql.TimeStamp, which is a wrapper for the Date component that adds fractional seconds to support a standard like SQL DATE. This class / type is not relevant or necessary for this question, but, in short, it is a date plus time.
• The database stores DATE objects as defined (using UTC as an offset from Java), but can transfer time, if configured in the database, to a different time zone. By default, most databases default to the time zone of the local server, which is a very bad idea. Ladies and gentlemen ... ALWAYS store DATE objects in UTC. Read on ...
• The time in the JVM and time zone must be correct. Since the Date object uses UTC, is offset calculation done for your time server? Think with a strong recommendation to set the server time to GMT + 0 (UTC).
• Finally, when we want to display DATE from the database (using JSF or something else), it must be set to GMT + 0 time zone and, if it is done on the server side, also ... your dates and time will ALWAYS be be consistent, referenced, and all good things. It remains only to display the time and IT, where a user agent (for example, for a web application) can be used to translate GMT + 0 time into the "local" time zone of users.

Summary: use UTC (GMT + 0) on the server, in the database, in your Java objects.

DATE and TIMESTAMP differ from the database in that TIMESTAMP contains an additional fraction of a second. Both use GMT + 0 (implied). JodaTime is the preferred calendar structure to solve all this, but will not fix inconsistent JVM issues for setting database time zones.

If application projects from the JVM to the database do not use GMT, due to the saving of daylight, adjusting the clock and all other regional games that play in the global local clock ... transaction times and everything else will be skewed, non-relational, inconsistent, etc. d.

Another good answer about data types: java.util.Date vs java.sql.Date

Also note that on Java 8 there are updates with improved date and time processing (finally), but this does not mean that the JVM server clock operates in one time zone and the database in another. At this moment, a translation always takes place. In every major (smart) client I work with, the time zones of the database and JVM servers are set to UTC for this very reason, even if their operations mainly occur in some other time zone.

Answer 3

But using DATETIME instead of a date will result in a one hour difference (depending on the time zone), which you can ignore if you are processing the date but not the time value. For me, the data coming from the mysql database were the correct values, but the difference occurred when using f: convertDateTime without the timeZone paramater parameter, which leads to the default use of GMT!

 <h:outputText value="#{test.dt}"> <f:convertDateTime pattern="yyyy-MM-dd HH:mm:ss" timeZone="CET"/> </h:outputText> 

works fine, but I think it won’t work when we get to CEST ....

Answer 4

I had the same problem. I don’t know the reason, but my workaround was as follows:

In the database, I changed the column type from DATE to DATETIME .

In the entity class, I changed the @Temporal annotation, but saved the DATE data type:

 @Temporal(TemporalType.TIMESTAMP) private Date myDate; 

Answer 5

I had the same problem, but I used JSF 2 as an interface. If you use JSF components, take a look at this other stackoverflow discussion and make sure that JSF 2 does not play according to the expected TimeZone rules. Designers implemented it to always use GMT. In my situation, this led to my Dates being disabled for 5 or 6 hours in the database, but displayed correctly.

JSF convertDateTime displays previous day

Answer 6

Had the same problem with SQL Server. The problem was using the old SQL JDBC driver. Had sqljdbc4.jar since April 2010, which was compatible with SQL 2000 and had a problem with dates returning for one or two days. Then it was updated to the latest driver or even until 2012, and the problem disappeared.