Uploaded image for project: 'OpenMRS Core'
  1. OpenMRS Core
  2. TRUNK-4252

Date fields are stored precise to the second in the DB, and MySQL 5.6 rounds milliseconds instead of truncating them




      Prior to MySQL version 5.6 the DATETIME datatype is only precise to the second, and in version 5.6 it supports higher-precision datetimes, but a column specified as "DATETIME" is also precise to the second. (To get millisecond precision in 5.6 you'd need to create a column as DATETIME(3).)

      Thus all the DATETIME fields in all existing OpenMRS installations running on MySQL are precise to the second.

      We use java.util.Date in OpenMRS, which has millisecond precision, so when saving an OpenMRS object to the database, date conversion happens. Prior to version 5.6, MySQL used to drop the millisecond component from a DATETIME when saving it. Starting in version 5.6, MySQL rounds a DATETIME, e.g. if you save a visit with startDatetime of 2014-02-05 14:35:17.641 it will be stored in the database rounded up to the next second: 2014-02-05 14:35:18.

      This can have several undesired effects. Take the following code snippet:

      Visit v = new Visit();
      // set Patient, VisitType, etc
      v.setStartDatetime(new Date());
      return "redirect:patient.page?ptId=" + v.getPatient().getId()

      In the 50% of cases where v.startDatetime was rounded up to the next second, the redirect takes us to the page for a patient who does not have an "active" visit, though they have a future one that will start in less than a second.

      One way to achieve the MySQL 5.5 behavior while running on version 5.6+ is to have a hibernate interceptor that drops the millisecond component of dates before writing them to the database.

        Gliffy Diagrams


            Issue Links



                darius Darius Jazayeri
                darius Darius Jazayeri
                0 Vote for this issue
                8 Start watching this issue