MySQL DateTime type not compatible with ISO 8601

[manucarrace]

Cygnus row mode. When setting a DateTime in MySQL it is not compatible with the date format in platform (ISO 8601).

It would be nice cygnus to transform de ISO 8601 format to MySQL DateTime.

https://es.wikipedia.org/wiki/ISO_8601
https://dev.mysql.com/doc/refman/8.0/en/datetime.html

[AlvaroVega]

recvTime or recvTimeTs?
MySQLBackend handles recvTime as string:
https://github.com/telefonicaid/fiware-cygnus/blob/master/cygnus-common/src/main/java/com/telefonica/iot/cygnus/backends/mysql/MySQLBackendImpl.java#L283
https://github.com/telefonicaid/fiware-cygnus/blob/master/cygnus-common/src/main/java/com/telefonica/iot/cygnus/backends/mysql/MySQLBackendImpl.java#L348

[AlvaroVega]

iot-cygnus                  | time=2019-06-11T09:58:37.129Z | lvl=INFO | corr=244f16b4-7d11-4c6c-b0fc-ca6e084e4bdd | trans=7b89379a-1dd5-4bf0-803f-f829317e99fa | srv=smartcity | subsrv=/ | comp=cygnus-ngsi | op=getEvents | msg=com.telefonica.iot.cygnus.handlers.NGSIRestHandler[316] : [NGSIRestHandler] Received data ({"subscriptionId":"5cff7b334d451a9151477b07","originator":"localhost","contextResponses":[{"contextElement":{"type":"thing3","isPattern":"false","id":"thing3:disp882","attributes":[{"name":"temperature","type":"Number","value":"25"},{"name":"TimeInstant","type":"DateTime","value":"2019-06-11T09:58:37.00Z"}]},"statusCode":{"code":"200","reasonPhrase":"OK"}}]})
iot-cygnus                  | time=2019-06-11T09:58:39.032Z | lvl=INFO | corr=244f16b4-7d11-4c6c-b0fc-ca6e084e4bdd | trans=7b89379a-1dd5-4bf0-803f-f829317e99fa | srv=smartcity | subsrv=/ | comp=cygnus-ngsi | op=persistAggregation | msg=com.telefonica.iot.cygnus.sinks.NGSIMySQLSink[628] : [mysql-sink] Persisting data at NGSIMySQLSink. Database (smartcity), Table (thing3_disp882_thing3), Fields ((recvTimeTs,recvTime,fiwareServicePath,entityId,entityType,attrName,attrType,attrValue,attrMd)), Values (('1560247117148','2019-06-11T09:58:37.148','/','thing3:disp882','thing3','temperature','Number','25','[]'),('1560247117148','2019-06-11T09:58:37.148','/','thing3:disp882','thing3','TimeInstant','DateTime','2019-06-11T09:58:37.00Z','[]'))

[AlvaroVega]

The same event for MySQL Sink

iot-cygnus                  | time=2019-06-11T11:23:50.434Z | lvl=DEBUG | corr=24d8ffe5-0f86-4b05-9d83-6d6d766e2a59 | trans=49d0c3a8-2c45-426c-b22d-89f32c6aecaa | srv=smartcity | subsrv=/ | comp=cygnus-ngsi | op=insertContextData | msg=com.telefonica.iot.cygnus.backends.mysql.MySQLBackendImpl[181] : Executing MySQL query 'insert into `thing3_disp33a_thing3` (recvTimeTs,recvTime,fiwareServicePath,entityId,entityType,attrName,attrType,attrValue,attrMd) values ('1560252229741','2019-06-11T11:23:49.741','/','thing3:disp33a','thing3','temperature','Number','44','[]'),('1560252229741','2019-06-11T11:23:49.741','/','thing3:disp33a','thing3','TimeInstant','DateTime','2019-06-11T11:23:49.00Z','[]')'

The same event for PostGIS Sink

iot-cygnus                  | time=2019-06-11T11:25:50.241Z | lvl=DEBUG | corr=24d8ffe5-0f86-4b05-9d83-6d6d766e2a59 | trans=2900e5c3-9a70-4105-ac71-faf247a45344 | srv=smartcity | subsrv=/ | comp=cygnus-ngsi | op=insertContextData | msg=com.telefonica.iot.cygnus.backends.postgresql.PostgreSQLBackendImpl[192] : Executing SQL query 'INSERT INTO smartcity.thing3_disp42a_thing3 (recvTimeTs,recvTime,fiwareServicePath,entityId,entityType,attrName,attrType,attrValue,attrMd) VALUES ('1560252349869','2019-06-11T11:25:49.869Z','/','thing3:disp42a','thing3','temperature','Number','11','[]'),('1560252349869','2019-06-11T11:25:49.869Z','/','thing3:disp42a','thing3','TimeInstant','DateTime','2019-06-11T11:25:49.00Z','[]')'

the difference is in recvTime which in MySQL is send ending without 'Z'

It seems that is done by CommonUtils.getHumanReadable(recvTimeTs, addUTC); because in MySQL sink addUTC is false.
https://github.com/telefonicaid/fiware-cygnus/blob/master/cygnus-ngsi/src/main/java/com/telefonica/iot/cygnus/sinks/NGSIMySQLSink.java#L474

[AlvaroVega]

So, instead of use an UTC like 2019-06-11T11:25:49.869Z MySQL sink is using 2019-06-11T11:23:49.741 but is not YYYY-MM-DD hh:mm:ss[.fraction] mysql datetime format.

[fgalan]

Fixed by PR #1662

[fgalan]

Pending to be tested in integration environment before closing.

[AlvaroVega]