Wrong average duration calculation when using the MySQL database

Current Situation

• The average duration calculation is used by Daily Plan (JOC-1189) and by the Notification interface (JOC-1134).
• When JOC uses the MySQL database, the calculation may be wrong.
• Cause:
  • The current implementation subtracts values from two columns in sql query. This has the consequence that instead of the date calculation a decimal calculation is used.
  • Example 1:

    • select cast('2022-02-28 12:00:10' as DATETIME)-cast('2022-02-28 12:00:00' as DATETIME);

      • returns expected 10 seconds
  • Example 2:

    • select cast('2022-02-28 12:00:00' as DATETIME)-cast('2022-02-28 11:59:00' as DATETIME);

      • returns the wrong result 4100 (120000-115900=4100) instead of expected 60 seconds

Desired Behaviour

• The calculation of the average duration for MySQL should work correctly.
  • Use of the MySQL TIMESTAMPDIFF function instead of a simple values substructions.
    • Example:

      • select TIMESTAMPDIFF(SECOND,cast('2022-02-28 11:59:00' as DATETIME),cast('2022-02-28 12:00:00' as DATETIME));

        • returns the expected 60 seconds