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.

What does `Date` resolve to? I hope `java.util.Date` (correct one) and not `java.sql.Date`? The simple reason is, when you write entities, the JPA is trying to map your entity properties to database columns (and types) and `java.sql.Date` is already a mapped type. So you must use `java.util.Date`. Please also note that e.g. `p:calendar` (PrimeFaces) is requiring `java.util.Date` and not `java.util.Calendar` (I bumped into it by myself). Refer to: https://javabydeveloper.com/temporal/ — Roland, Oct 25 '17 at 09:03

score 29 · Answer 1 · edited May 23 '17 at 12:34

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

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

I don't understand why timezone is relevant here. The field in question is **Date** - no hour information is involved. So timezone adjustments should not affect the results. To me it looks like an EclipseLink bug, as @wobblycogs implied in his answer. — gamliela, May 07 '15 at 15:37
The question references the JPA Temporal type and associated java.util.Date objects, which include a time component. When just looking at the Day component for example, presently it is May 9th in New York yet May 10th in Europe. Thus yes, time and timezone are always a factor when evaluating "when" something occurred, as experienced in the posting. It is not specific to EclipseLink as this situation has occurred and will in all JPA implementations or indeed any Java + DB environment since their base clock references can be in different timezones. — Darrell Teague, May 10 '15 at 04:00
I agree that timezone is a factor when you ask "when" something occurred. For example, when the day "10/5/2015" has started at specific place on earth. But as long as I work with pure date values, without exact time component, the notation of "10/5/2015" is the same in all places. Of course, there is a question what happens when you call `getTime()` on such date, and that's where things get complicated with timezones. — gamliela, May 10 '15 at 08:47
To clarify: A String.equals() call on the above reference will return true. Conversion to a Date (using DateFormat for example) however will NOT return equal for that same value if the timezone values are different. A date is a point-in-time as a long value of the number of milliseconds since the Epoch. Two dates will therefore only be consistent and equal (and implied to be "midnight" on that day) if the time components are zero and based in (the same) UTC time-zone. getTime() in that case will always return zero (as it should - meaning "midnight UTC on THAT day"). — Darrell Teague, Apr 26 '16 at 21:44
This helped me. It was the timezone issue. I fixed this by setting the timezone globally for my app. TimeZone.setDefault(TimeZone.getTimeZone("UTC")); — ChrisThompson, Mar 06 '20 at 20:38

score 5 · Accepted Answer · answered Mar 14 '11 at 15:47

5

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.

answered Mar 14 '11 at 15:47

wobblycogs

4,083
7
37
48

Depends on the JPA implementation. DataNucleus, for example, allows the user to configure what timezone to store things in. Other impls may offer something similar – DataNucleus Mar 14 '11 at 16:47
I'm currently using EclipseLink, I'm pretty sure that's not part of the problem though. What seems to be happening is the converter element is assuming that it should display the date in GMT even though it's being provided in BST. – wobblycogs Mar 14 '11 at 20:03
This may not be a good fix. As stated, "... the timezone is _currently_ GMT (because it's winter..." - so what happens in Summer in that locale? Fight with tooth and nail to get the layers synchronized on GMT with no offsets or changes and the rest will fall in place. Otherwise the shifting time zones on any layer will always cause errors at some (possibly unknown) future point in time. – Darrell Teague Jul 21 '14 at 18:32
Ah, okay. I saw not this answer when I wrote my first comment. Please update your question accordingly, mentioning that you indeed use `java.util.Date`. – Roland Oct 25 '17 at 09:05
Also check BalusC's answer here: https://stackoverflow.com/questions/7490954/set-a-default-time-zone-for-fconvertdatetime – Roland Oct 25 '17 at 09:09

score 2 · Answer 3 · answered Mar 15 '11 at 09:54

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;

Michael Glockenstein · Answer 4 · 2011-03-19T11:30:04.680

2

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

edited Mar 19 '11 at 11:30

answered Mar 19 '11 at 11:24

Michael Glockenstein

21
2

A date is a point in time. As in the previous examples, the "date only" component may be "01/01/2003" with an IMPLIED time-zone of say EST (which changes due to day-light savings time). So, to say that this transaction date is not time-dependent, that occurred in China, when the date THEN was ACTUALLY 01/02/2003 in the local timezone, for example, is incorrect. The time component can only be ignored if the timezone was logged as UTC. Even then, when rendering the date, one implies the timezone is UTC from that point forward and what "day it was" in any other timezone is "unknown". – Darrell Teague Aug 04 '15 at 09:57

score 1 · Answer 5 · edited May 23 '17 at 11:54

1

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

edited May 23 '17 at 11:54

Community

1
1

answered Mar 27 '14 at 17:43

Revoman

222
2
6

It is actually the JVM doing the conversion (not JSF) based on the server defined time-zone, which if different from the database time-zone... all bets are off for consistency. – Darrell Teague Jul 21 '14 at 18:34

score 1 · Answer 6 · answered Jan 23 '18 at 08:57

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.

answered Jan 23 '18 at 08:57

Robert Niestroj

15,299
14
76
119

I had the same problem the sqljdbc4 should not be used any more, the com.microsoft.sqlserver » mssql-jdbc are the right drivers. Thanks for this answer. Very Hard to debug SQL Server related issues – Rishi Ahuja Dec 03 '19 at 08:07

JPA TemporalType.Date giving wrong date

6 Answers6

Linked