Geopackage with DateTime field (temporal selection) behave different for painting and attributetable

[rduivenvoorde]

Testing Temporal Vector features handling for #40989 I encountered the following issue.

It seems that the (temporal?) filter used for painting and the i-tool are different from the one used for showing the attribute table.

See below:

• every features has a datetime "Time" and a duration of 1 hour
• styling adjusted to be able to see that features are overlapping (they should NOT)
• showing 1 hour of data (7-8), you see 2 hours (7-9), both painted AND when you use the i-tool.
• but when opening the attribute table you see only (rightfully) the 7(-8) hours

To reproduce: open project + data below:

geopackage_datetime.zip

Change temporal controller, and see that there are (hourly/spatially) overlapping features.
Move the temporal controller 1 hour
Select i-tool and select 1 cell in the dark part of the cloud: 2 hours
Now open attribute table: you will only see 1 hour data.

To retest: save the gpkg to a shapefile, and do the same. The shapefile behaves as it should be :-(
(note: you have to use an expression to create a virtual DateTime field for shp's as a shp does not have DateTime columns itself)

[rduivenvoorde]

Another format: geojson you can test without expressions:

cloud.zip

[rduivenvoorde]

Adding much smaller dataset here, holding both a gpkg AND a shp file with the same data:

smallcloud.zip

Currently in the temporal controller, the shape works (as it should), but the gkpg seems to return 2 classes (instead of one).

BUT if you add a virtual (datetime) field and use THAT field, then it just works as it should ...

So I thought to check if the QDateTimes differ, below I select one feature, and show the original QDatetime (called 'Time') and the virutal QDateTime (called 'datetime')

To me: they look EXACT the same, BUT they behave in the renderer differently...

iface.activeLayer().selectedFeatures()[0].attribute('Time')
PyQt5.QtCore.QDateTime(2019, 9, 1, 7, 0)
iface.activeLayer().selectedFeatures()[0].attribute('datetime')
PyQt5.QtCore.QDateTime(2019, 9, 1, 7, 0)

>>> iface.activeLayer().selectedFeatures()[0].attribute('datetime').toMSecsSinceEpoch()
1567314000000
>>> iface.activeLayer().selectedFeatures()[0].attribute('Time').toMSecsSinceEpoch()
1567314000000

>>> iface.activeLayer().selectedFeatures()[0].attribute('Time').toUTC()
PyQt5.QtCore.QDateTime(2019, 9, 1, 5, 0, 0, 0, PyQt5.QtCore.Qt.TimeSpec(1))
>>> iface.activeLayer().selectedFeatures()[0].attribute('datetime').toUTC()
PyQt5.QtCore.QDateTime(2019, 9, 1, 5, 0, 0, 0, PyQt5.QtCore.Qt.TimeSpec(1))

>>> iface.activeLayer().selectedFeatures()[0].attribute('datetime').timeZone().country()
151
>>> iface.activeLayer().selectedFeatures()[0].attribute('Time').timeZone().country()
151

>>> QTimeZone().abbreviation(iface.activeLayer().selectedFeatures()[0].attribute('datetime'))
''
>>> QTimeZone().abbreviation(iface.activeLayer().selectedFeatures()[0].attribute('Time'))
''

>>> iface.activeLayer().selectedFeatures()[0].attribute('Time').timeZone() == iface.activeLayer().selectedFeatures()[0].attribute('datetime').timeZone()
True

>>> iface.activeLayer().selectedFeatures()[0].attribute('Time') == iface.activeLayer().selectedFeatures()[0].attribute('datetime')
True

[rduivenvoorde]

I thought to check if it was polygon related, so I saved the polygons to (centroid) points, and then styled them with an arrow-marker with a random rotation to distinguish if there was one or more point rendered.

As you can see both gpgk's do double rendering (AND showing in Identify results),
but the attribute table rightfully reports ONLY 8:00 clock for the smallcloudpoints (= gpkg....)

I'm going to check now if it is maybe provider specific, so will test in postgis now...

[rduivenvoorde]

Ok, tested both Postgis and Flatgeobuf (and even Mapinfo tab file) format and all work fine...

Does gpkg handle expressions differently then other providers? All are handled by gdal isn't it?

[rduivenvoorde]

Ok, I think I have a clue...

OGR apparently treats the DateTime queries differently (ouch, I hit that before), and THAT is the reason that QGIS returns different result sets for different data formats....

To test/see: testdata.zip that zip contains both a gpkg and a flatgeobuf of the same data with one DateTime column.
Unzip in a dir and check the output of the 'query' that is returned by QGIS (well in my version....):

ogrinfo -sql "select * from smallcloudpoints where (Time > '2019-09-01T06:00:00Z') and (Time < '2019-09-01T08:00:00Z') " smallcloudpoints.fgb

this rightfully returns ONLY the 7:00 features...

Now the same for the gpkg:

ogrinfo -sql "select * from smallcloudpoints where (Time > '2019-09-01T06:00:00Z') and (Time < '2019-09-01T08:00:00Z') " smallcloudpoints.gpkg

THAT returns both 7:00 AND 8:00 features ????

Note that '2019-09-01T06:00:00Z' or '2019-09-01T06:00:00.000Z' is the right format to write a datatime in gpkg: see http://www.geopackage.org/spec/#r5

BUT... to make it worse: now remove the Z from the datetime:

ogrinfo -sql "select * from smallcloudpoints where (Time > '2019-09-01T06:00:00') and (Time < '2019-09-01T08:00:00') " smallcloudpoints.gpkg

NOW it returns the right (7:00 only) features....

Will create a upstream issue I think.

Done: OSGeo/gdal#3423

[rouault]

When you use SQL with OGR, the SQL engine depends on the underlying data source. In the case of GeoPackage, this is SQLite that evaluates the query. As in that geopackage, date-times value are stored like '2019-09-01T08:00:00' and that this is a string literal, and that the Time < '2019-09-01T08:00:00Z' comparison is done in the string 'space', '2019-09-01T08:00:00' < '2019-09-01T08:00:00Z' is actually true. Basically SQLite is not time-aware.
The OGR SQL dialect used for non-database based datasources is (a bit more) time-aware.

[rduivenvoorde]

Thanks @rouault !

So do I understand correctly that OGR in a gpkg interpretets ( ;-) not sure if that is english) the '2019-09-01T08:00:00' as a string and '2019-09-01T08:00:00Z' as a datetime?

Mmm, nope; in the db (looking with 'sqlitebrowser') there is indeed "2019-09-01T06:00:00", but ogrinfo reports that as DateTime:

OGRFeature(smallcloudpoints):30449
  Cell (Integer64) = 20106
  Time (DateTime) = 2019/09/02 06:00:00
  Value (Real) = 0.00924345292150974
  POINT (3.97310630272895 52.643215041253)

So as you say: sqlite is just doing a string comparison... (which works if you keep the exact same datetime-syntax).

Would it not be safer then to 'report' it (both in OGR and thus in QGIS?) as a string?

And maybe only if you really use the 'right' datetime-format-string report (and use) a column as DateTime?

(sidenote: will it be possible to push a datatime (and some spatial types) into sqlite-spec in any future?)

[rouault]

When OGR reads a DATETIME declared column in a GeoPackage , it interprets its as such and uses the OFTDateTime OGR data type (which for historical reasons is displayed for the user in the YYYY/MM/DD HH:MM:SS format). But when you issue a SQL request, OGR is just a pass-through to SQLite.

Would it not be safer then to 'report' it (both in OGR and thus in QGIS?) as a string?

OGR has a model for the DateTime data type so it is logical/expected that it uses it. If OGR reported all native data types in their raw form, the burden would be on QGIS and other clients to understand that YYYYMMDD for a Shapefile is a date, etc.

If see your issue, but I don't have a ready-made solution. One could also argue that comparing date-times without time zone and date-times with time zone is a undefined comparison...

[rduivenvoorde]

Yes, you are right. I'm now trying to 'fix' my gpkg...

IF it works when I (myself) use the right syntax, then I rest my case (and sleep a lot better... this has eaten a lot of my time already :-) ). Thanks again!

[rduivenvoorde]

Ok, this is harder then I thought:

Trying to create a geopackage in QGIS from a csv in which the 'time' field correctly is defined in format: YYYY-MM-DDTHH:MM:SS.SSSZ

Result: QGIS only does local time... so ... just removes the 'Z' :-(

Which... makes it unusable for me to use it in ... QGIS with temporal controller...

Sigh... time is hard (and gpkg too)

[rduivenvoorde]

Okay... for those interested:

• I created a gpkg with my first vrt file from a csv, using ogr2ogr, with Time data defined in format as: 2019-09-01T08:00:00Z
  ogrinfo then created a Time column of type String... which worked in QGIS though ??

• then I created a gpkg with my second vrt file (defining the column type as DateTime:
  ogrinfo create a Time column of type DateTime... which also worked in QGIS ... ??

NOW I'm really eager to know which exact 'sql' is created (by QGIS) from this QGIS expression:
"("Time" > make_datetime(2019,9,1,18,0,0) AND "Time" < make_datetime(2019,9,1,20,0,0)) OR "Time" IS NULL"
because as QGIS is NOT timezone aware (but QDateTime is....) I really do not know what to expect...

But from the experiments above, I really don't know anymore.

We really need timezone awareness in QGIS

Happy to provide assistance/data/sponsoring to anybody willing to dive into this and do this proper.

[nyalldawson]

@nirvn you've researched this area extensively in the past -- can you weigh in here please?

[rldhont]

I have facing this bug, when I tried to create a test for QGIS Server WMS Time Dimension #43417

[pathmapper]

There's an issue regarding DATETIME timestamps with Z (zero) timezone which might also play a role here-> #48393