JPA TemporalType.Date giving wrong date

Question

I have a class that has a date field representing a "valid from" date for a piece of data. It is defined like this:

@Temporal( TemporalType.DATE )
private Date validFrom;

All seems to be working fine right up the the point where I pull the date from the database and display it. If I select the date 18-Sep-2003 in the front end then save it when I check in the database sure enough that is the date held (database is MySQL 5.5.9 column type is DATE). However when I pull up a list records the date shown is 17 Sep 2003 - one day earlier.

If I choose a date early or late in the year like 26 Mar 2003 or 25 Dec 2003 everything is fine so I guessing this is something to do with daylight saving but where is the error creeping in? Since the database appears to be holding the correct date I'm guessing it must be when JPA is converting back into a java.util.Date - is java.util.Date the best class to use for a date? I've seen a few examples where people use Calendar but that seems pretty heavy weight and I'm not sure how well it will work with a JSF based front end.

Answer 1

Had the same problem with SQL Server. The problem was an old SQL JDBC driver. Had sqljdbc4.jar from April 2010 which was SQL 2000 compatible and had the problem with dates going back one or two days. Then updated to the latest driver or even to one from 2012 and the problem went away.

Answer 2

Very sorry but all of the answers so far are generally incorrect. The answer is quite simple but requires that we separate five points:

1. DATE = java.sql.Date, which is a wrapper around java.util.Date that is the number of milliseconds since the Epoch in the UTC time-zone. So this has the year/month/date/hours/minutes/seconds in a fixed GMT+0 (UTC) time-zone. Note however that java.sql.Date sets the time components to zero!
2. TIMESTAMP = java.sql.TimeStamp which is a component wrapper around Date that adds fractional seconds to support the SQL DATE type standard. This class/type is not relevant or needed for this question but in short this has the date plus the time.
3. The database stores DATE objects as defined (using UTC as the offset from Java) but may translate the time if configured in the database to be in a different time-zone. By default most databases default to the local server timezone, which is a very bad idea. Ladies, gentlemen ... ALWAYS store DATE objects in UTC. Read on...
4. The time in the JVM and timezone needs to be right. Since the Date object is using UTC, is an offset getting calculated for your server-time? Consider that with the strong recommendation that server time be set to GMT+0 (UTC).
5. Finally when we want to render the DATE from the database (using JSF or whatever), it should be setup to be GMT+0 timezone and, if done from the server up side also ... your dates and times will ALWAYS be consistent, referential and all good things. All that is left is to render the time and THIS is where the user-agent (for a web-application for example) could be used to translate the GMT+0 time to the users "local" timezone.

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

DATE and TIMESTAMP are only different from a database perspective in that TIMESTAMP carries additional fractions of seconds. Both use GMT+0 (implied). JodaTime is a preferred calendar framework to deal with all of this but won't fix the issues of mismatched JVM to database time-zone settings.

If application designs from JVM to the DB do not use GMT, due to daylight-savings, clock adjustments and all kinds of other regional games that are played in the world local clocks ... the times of transactions and everything else will forever be skewed, non-referential, inconsistent, etc.

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

Also note that Java 8 has updates with better date/time handling (finally) but this does not fix having the server clock the JVM is running on be in one timezone and the database be in another. At this point there is always translation happening. In every large (smart) client I work with, the database and JVM server timezones are set to UTC for this very reason, even if their operations largely occur in some other timezone.

Answer 3

I was experiencing this same issue but I was using JSF 2 as the front end. If you are using JSF components look at this other stackoverflow discussion and see that JSF 2 does not play by the expected TimeZone rules. The designers Implemented it to always use GMT. In my situation this caused my Dates to be off by 5 or 6 hours in the database, but display correctly.

JSF convertDateTime renders the previous day

Answer 4

But using DATETIME instead of date will lead to a one hour (ore more depending the time zone) difference, which you may ignore if you handle a date, but not a time value. For me the data coming from the mysql database was the correct value, but the difference came in when using the f:convertDateTime without a timeZone paramater, which leads to a default of using GMT!

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

works fine, but I think this will work no more when we switch to CEST ....

Answer 5

I had the same problem. Don't know the reason but my workaround was the following:

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

In the entity class I changed the @Temporal annotation but kept the datatype Date:

@Temporal(TemporalType.TIMESTAMP)
private Date myDate;

Answer 6

After much experimenting and searching I'm pretty sure I've found the cause of the problem. The date is held in a java.util.Date which comes with all the baggage of time and a timezone. It would seem that JPA is reading the date 18 Sep 2003 from the database and then populating the date like this: "Thu Sep 18 00:00:00 BST 2003" - notice the timezone has been set to BST probably because it wasn't explicitly set by the database. Anyway, it is necessary to format the output in the JSF page if you only want to see the date like this:

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

This, however, assumes that the timezone is whatever is currently in force on the machine. In my case the timezone is currently GMT (because it's winter) so when presented with the date "Thu Sep 18 00:00:00 BST 2003" it converts it to GMT by subtracting one hour leaving the display showing 17 Sep 2003.