Hibernate ORM
  1. Hibernate ORM
  2. HHH-8617

Unexpected (changed) behavior of @Temporal(TemporalType.DATE) columns

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 4.2.6
    • Fix Version/s: 4.2.7.SP1, 4.3.0.CR1
    • Component/s: core
    • Labels:
      None
    • Environment:
      Java 7, user.timeZone "Europe/Berlin", MySQL with UTC timezone
    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Requires Release Note:
      Affirmative
    • Last commented by a user?:
      true

      Description

      Hi there,

      first of all, thanks for your continuing work for hibernate.

      Hibernate 4.2.6 shows a new behavior regarding the handling of

      @Temporal(TemporalType.DATE)
      private Calendar refDate;

      columns.

      Prior to 4.2.6 the seems to be handled like:

      • Take the date from the database and treat it like midnight
      • Then convert it to the userTimezone

      Now it seems to bee

      • Take the date from the database and treat it like midnight
      • Then convert it to the userTimezone
      • Strip the time

      This results in a calendar with a very different value than before. Technically it's probably more correct, but apart from breaking stuff, it's quite a different and unexpected behavior than @Temporal(TemporalType.TIMESTAMP) columns.

      A result from 4.2.5 and prior for a date column with the value '2013-10-15' was
      // Hibernate 4.2.5
      // Tue Oct 15 02:00:00 CEST 2013
      that can be correctly converted to a Joda Local Date with forced UTC timezone using
      new LocalDate(foobar.getRefDate(), ISOChronology.getInstanceUTC())
      resulting in exactly 2013-10-15

      Now hibernate 4.2.6 returns:
      // Hibernate 4.2.6
      // Tue Oct 15 00:00:00 CEST 2013

      This is quite different date.

      I'd vote to restore the old way as it is consistent with the conversion in TIMESTAMP columns.

      At least the changed should have been in the release notes as it is very likely to break stuff.

      I've created a little test case. You'll need a mysql datebase hibernate_tests for it.

      https://github.com/michael-simons/possible_hibernate_426_bug_with_date

      Thanks in advance.

        Issue Links

          Activity

          Hide
          Brett Meyer added a comment -

          Michael Simons, in my opinion, your fix makes things worse. With Calendar-as-a-Date (or plain Date), foobar.getRefDate().getTime().toString() should return 00:00:00 in the users timezone. Nearly all JDBC drivers do this. Your test case's entity returns the following refDate toString on most JDBC drivers, both before and after HHH-8517 is applied.

          Tue Oct 22 00:00:00 EDT 2013

          Obviously w/ your fix, an hour can be there depending on the offset. Like Steve said, what we were shooting for really is the expected behavior.

          Show
          Brett Meyer added a comment - Michael Simons , in my opinion, your fix makes things worse. With Calendar-as-a-Date (or plain Date), foobar.getRefDate().getTime().toString() should return 00:00:00 in the users timezone . Nearly all JDBC drivers do this. Your test case's entity returns the following refDate toString on most JDBC drivers, both before and after HHH-8517 is applied. Tue Oct 22 00:00:00 EDT 2013 Obviously w/ your fix, an hour can be there depending on the offset. Like Steve said, what we were shooting for really is the expected behavior.
          Hide
          Michael Simons added a comment -

          Brett, i thank for still looking into this.

          If the timezone and the time components are so irrelevant, than please explain to my, why this fails:

          @Test
          	public void update() throws SQLException {
          		this.entityManager.getTransaction().begin();
          		Foobar foobar = new Foobar();
          		Calendar hlp = Calendar.getInstance();
          		// So this is now 2013-10-15 at Midnight CEST…
          		hlp.set(2013, 10-1, 15, 0, 0, 0);
          		foobar.setRefDate(hlp);
          		foobar.setTakenOn(Calendar.getInstance());
          		this.entityManager.persist(foobar);		
          		this.entityManager.getTransaction().commit();
          		final int id = foobar.getId();
          
          		final DateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
          		
          		Session session = entityManager.unwrap(Session.class);
          		session.doWork(new Work() {			
          			@Override
          			public void execute(Connection connection) throws SQLException {
          				ResultSet rs = connection.createStatement().executeQuery("Select ref_date from foobars where id = " + id);
          				rs.next();
          				Date date = rs.getDate(1);
          				Assert.assertEquals("2013-10-15", sf.format(date));
          				System.out.println(date);				
          			}
          		});		
          	}
          

          This only works if i set the calendar to hlp.set(2013, 10-1, 15, 2, 0, 0), otherwise the date ends up as 2013-10-14 in the database.

          mysql> select * from foobars;
          +----+------------+---------------------+
          | id | ref_date   | taken_on            |
          +----+------------+---------------------+
          |  1 | 2013-10-15 | 2013-10-15 05:29:21 |
          | 12 | 2013-10-14 | 2013-10-23 05:49:32 |
          | 13 | 2013-10-14 | 2013-10-23 05:52:06 |
          | 14 | 2013-10-14 | 2013-10-23 05:52:39 |
          +----+------------+---------------------+
          

          So i don't think we're done here.

          Show
          Michael Simons added a comment - Brett, i thank for still looking into this. If the timezone and the time components are so irrelevant, than please explain to my, why this fails: @Test public void update() throws SQLException { this .entityManager.getTransaction().begin(); Foobar foobar = new Foobar(); Calendar hlp = Calendar.getInstance(); // So this is now 2013-10-15 at Midnight CEST… hlp.set(2013, 10-1, 15, 0, 0, 0); foobar.setRefDate(hlp); foobar.setTakenOn(Calendar.getInstance()); this .entityManager.persist(foobar); this .entityManager.getTransaction().commit(); final int id = foobar.getId(); final DateFormat sf = new SimpleDateFormat( "yyyy-MM-dd" ); Session session = entityManager.unwrap(Session.class); session.doWork( new Work() { @Override public void execute(Connection connection) throws SQLException { ResultSet rs = connection.createStatement().executeQuery( "Select ref_date from foobars where id = " + id); rs.next(); Date date = rs.getDate(1); Assert.assertEquals( "2013-10-15" , sf.format(date)); System .out.println(date); } }); } This only works if i set the calendar to hlp.set(2013, 10-1, 15, 2, 0, 0), otherwise the date ends up as 2013-10-14 in the database. mysql> select * from foobars; +----+------------+---------------------+ | id | ref_date | taken_on | +----+------------+---------------------+ | 1 | 2013-10-15 | 2013-10-15 05:29:21 | | 12 | 2013-10-14 | 2013-10-23 05:49:32 | | 13 | 2013-10-14 | 2013-10-23 05:52:06 | | 14 | 2013-10-14 | 2013-10-23 05:52:39 | +----+------------+---------------------+ So i don't think we're done here.
          Hide
          Michael Simons added a comment -

          This way it's even more fun:

          /**
          	 * When the TZ and Time of a date column are not relevant, than explain to me,
          	 * why the date ends up as 2013-10-14 in my database....
          	 * 
          	 * Starting with
          	 * INSERT INTO foobars(id, ref_date, taken_on) values (2, '2013-10-15', '2013-10-15 05:29:21');
          	 * 
          	 * @throws SQLException
          	 */
          	@Test
          	public void update() throws SQLException {
          		final Session session = entityManager.unwrap(Session.class);
          						
          		final DateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
          		
          		this.entityManager.getTransaction().begin();
          		Foobar foobar = this.entityManager.find(Foobar.class, 2);
          		// Update some column to force an update...
          		foobar.setSomeOtherColumn("blah");				
          		this.entityManager.getTransaction().commit();
          				
          		final int id = foobar.getId();
          		session.doWork(new Work() {			
          			@Override
          			public void execute(Connection connection) throws SQLException {
          				ResultSet rs = connection.createStatement().executeQuery("Select ref_date from foobars where id = " + id);
          				rs.next();
          				Date date = rs.getDate(1);
          				Assert.assertEquals("2013-10-15", sf.format(date));
          				System.out.println(date);				
          			}
          		});
          	}
          

          Works perfectly in Hibernate 4.2.5 and doesn't in 4.2.6. You cannot blame the user (or me in this case) for bringing any time into this test case.

          Sorry, but your implementation just doesn't work out and i'd be more than happy to help.

          Show
          Michael Simons added a comment - This way it's even more fun: /** * When the TZ and Time of a date column are not relevant, than explain to me, * why the date ends up as 2013-10-14 in my database.... * * Starting with * INSERT INTO foobars(id, ref_date, taken_on) values (2, '2013-10-15', '2013-10-15 05:29:21'); * * @ throws SQLException */ @Test public void update() throws SQLException { final Session session = entityManager.unwrap(Session.class); final DateFormat sf = new SimpleDateFormat( "yyyy-MM-dd" ); this .entityManager.getTransaction().begin(); Foobar foobar = this .entityManager.find(Foobar.class, 2); // Update some column to force an update... foobar.setSomeOtherColumn( "blah" ); this .entityManager.getTransaction().commit(); final int id = foobar.getId(); session.doWork( new Work() { @Override public void execute(Connection connection) throws SQLException { ResultSet rs = connection.createStatement().executeQuery( "Select ref_date from foobars where id = " + id); rs.next(); Date date = rs.getDate(1); Assert.assertEquals( "2013-10-15" , sf.format(date)); System .out.println(date); } }); } Works perfectly in Hibernate 4.2.5 and doesn't in 4.2.6. You cannot blame the user (or me in this case) for bringing any time into this test case. Sorry, but your implementation just doesn't work out and i'd be more than happy to help.
          Hide
          Brett Meyer added a comment -

          Ok, we're conceding that this needs revisited, although I still don't think the solution you provided was 100%. I'm reverting HHH-8517 and releasing 4.2.7.SP1.

          Show
          Brett Meyer added a comment - Ok, we're conceding that this needs revisited, although I still don't think the solution you provided was 100%. I'm reverting HHH-8517 and releasing 4.2.7.SP1.
          Hide
          Michael Simons added a comment -

          Thanks, Brett.

          The idea behind my proposal is based on the assumption that at least the MySQL JDBC driver

          • handles time correct
          • but converts them into the user JVM timezone

          So the only way to be save with 00:00:00 is in UTC, therefore stripping is done there. Than the resulting value is converted back to the JVM timezone.

          Regarding your comment https://hibernate.atlassian.net/browse/HHH-8617?focusedCommentId=54459&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-54459 i wonder why the TimeZone is EDT at all, because i set the default to something else in my test. Probably you're referring to use my proposal in the Calendar type, i guess.

          But anyway, yes, there must be an hour (or hour and 30 minutes offsets, depending on the weird zones out there), in different zones in all cases there is a transformation from server UTC to JVM timezone in accordance with the JDBC spec.

          I got that you want Hibernate JPA act like the JDBC spec but i also think that this is part of the problem (please, no offense, just my opinion).

          Anyway, thanks for the discussion and again for looking into this.

          Show
          Michael Simons added a comment - Thanks, Brett. The idea behind my proposal is based on the assumption that at least the MySQL JDBC driver handles time correct but converts them into the user JVM timezone So the only way to be save with 00:00:00 is in UTC, therefore stripping is done there. Than the resulting value is converted back to the JVM timezone. Regarding your comment https://hibernate.atlassian.net/browse/HHH-8617?focusedCommentId=54459&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-54459 i wonder why the TimeZone is EDT at all, because i set the default to something else in my test. Probably you're referring to use my proposal in the Calendar type, i guess. But anyway, yes, there must be an hour (or hour and 30 minutes offsets, depending on the weird zones out there), in different zones in all cases there is a transformation from server UTC to JVM timezone in accordance with the JDBC spec. I got that you want Hibernate JPA act like the JDBC spec but i also think that this is part of the problem (please, no offense, just my opinion). Anyway, thanks for the discussion and again for looking into this.

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development