apply: error deleting rows due to date/time mismatch

[wonder-sk]

When using db-sync, it was seen (MerginMaps/db-sync#50) that if a table is synced from geopackage to postgresql, some rows are removed and then the diff is applied back on the geopackage, it may happen that applying the diff fails due to subtle difference in how date/time is stored.

The original data may contain date/time values like this: 2021-03-16T00:00:00
Diff returning from postgresql was referring to values including Zulu timezone: 2021-03-16T00:00:00Z

When deleting rows, this mismatch of what is stored in SQLite and what is stored in diff causes the error.

A temporary fix for this issue is to add the "Z" suffix to the source data, in sqlite the batch command may look like this:

UPDATE fruit_monitor SET date = date || 'Z';

[wonder-sk]

We already have special handling of date/time values when it comes to creation of UPDATE statements (https://github.com/lutraconsulting/geodiff/blob/master/geodiff/src/drivers/sqlitedriver.cpp#L497), we should apply that also when creating/applying DELETE statements.

sqlite> select '2021-03-16T00:00:00' = '2021-03-16T00:00:00Z';
0
sqlite> select datetime('2021-03-16T00:00:00') = datetime('2021-03-16T00:00:00Z');
1

[HugoP27]

Hi @wonder-sk is this scheduled in your development timeline or could we speed it up somehow, maybe fund a part of the development? please let me know. cheers