Uploaded image for project: 'Red Hat Process Automation Manager'
  1. Red Hat Process Automation Manager
  2. RHPAM-3716

Precision time (milliseconds) for datime columns should be taken into account for MySQL and MariaDB

    XMLWordPrintable

Details

    • Enhancement
    • Resolution: Done
    • Minor
    • 7.12.0.GA
    • 7.11.0.GA
    • jBPM Core
    • None
    • False
    • False
    • ER1
    • Undefined
    • 2021 Week 28-30 (from Jul 12), 2021 Week 31-33 (from Aug 2)

    Description

      `datetime` columns in MySQL/MariaDB are not storing any precision for the time like milliseconds, apart from the `dd/mm/YYYY hh:mm:ss`.

      As per official and latest documentation from MySQL v8.0:

      If omitted, the default precision is 0

       

      Below is a snippet taken from the official doc[1]:

       

      MySQL has fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision: 
      To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, 
      or TIMESTAMP, and fsp is the fractional seconds precision.
      ...
       
      The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. 
      If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)
      

       

      So currently we are omitting the precision in both code and DDL scripts, thus we cannot work with milliseconds related stuff, whereas for the rest of supported and certified database we are currently working with precision time of milliseconds.

      Also, having a look at the DDL scripts for both MySQL and MariaDB, I can see the following commented code from Maciej 5 years ago:

      `datetime(6), to be used with mysql 5.6.4 that supports millis precision` [2] and [3]

       

      Expected: datetime column format with milliseconds precision:  `dd/mm/YYYY hh:mm:ss.SSS`.

       

      [1] https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html

      [2] https://github.com/kiegroup/jbpm/blob/master/jbpm-db-scripts/src/main/resources/db/ddl-scripts/mysql5/mysql5-jbpm-schema.sql#L5

      [3] https://github.com/kiegroup/jbpm/blob/master/jbpm-db-scripts/src/main/resources/db/ddl-scripts/mysqlinnodb/mysql-innodb-jbpm-schema.sql#L5

      Attachments

        Issue Links

          Activity

            People

              antferna Antonio Fernandez Alhambra (Inactive)
              antferna Antonio Fernandez Alhambra (Inactive)
              Gonzalo Muñoz Fernández Gonzalo Muñoz Fernández
              Gonzalo Muñoz Fernández Gonzalo Muñoz Fernández
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: