SpatiaLite provider changes timestamp format

[anitagraser]

Describe the bug

I have a SpatiaLite (https://www.dropbox.com/s/4dtkpmlyhs741bn/geolife-beijing.sqlite?dl=0) with string fields that contain timestamps in %Y-%m-%d %H:%M:%S format. However, if I access the min or max values through the provider, the format changes to %Y/%m/%d %H:%M:%S

How to Reproduce

>>> l = iface.activeLayer()
>>> p = l.dataProvider()
>>> p.minimumValue(2)
'2008/11/11 00:00:00'

>>> f = QgsFeature()
>>> l.getFeatures().nextFeature(f)
>>> f['t_datetime']
'2008-11-11 00:00:01'

QGIS and OS versions

Tested with 3.4.8 on Win 10 and Ubuntu

Additional context

This behavior messed up TimeManager https://github.com/anitagraser/TimeManager/issues/218

[m-kuhn]

First insights, this is directly extracted from ogr.

See e.g.

ogrinfo -sql 'SELECT MIN("t_datetime") FROM "geolife_beijing"' geolife-beijing.sqlite              
INFO: Open of `geolife-beijing.sqlite'
      using driver `SQLite' successful.

Layer name: SELECT
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
MIN("t_datetime"): DateTime (0.0)
OGRFeature(SELECT):0
  MIN("t_datetime") (DateTime) = 2008/11/11 00:00:00

QGIS handles it as string field internally and not as date, whereas ogr handles it as date (see above)

[m-kuhn]

@anitagraser after the above insights, the problem to me seems to be that QGIS detects this as string even though ogr treats it as date. Reading your original message you seem to expect strings.

I suppose was properly exposing these as DateTime in QGIS would also work for you?

[anitagraser]

@m-kuhn What TimeManager needs is consistency. It was inferring the timestamp format from the value it received from p.minimumValue(timefield). Then it used this format to construct a subsetString filter. This failed because the two formats were different.

As a workaround, TimeManager now uses the time field value of the first feature rather than the p.minimumValue(timefield)

Yes, I think exposing these values as DateTime would work. I assume that's what the PostGIS provider does?

[m-kuhn]

Yes, I think exposing these values as DateTime would work. I assume that's what the PostGIS provider does?

Yes, that's what Postgres does. I'll have a look.

Sqlite based formats are a bit more delicate unfortunately because sqlite doesn't know a native DateTime format, which means ogr/qgis abstracts this on top.

Btw, the inconsistency also exists on pure ogr level, see below. But I'm confident by switching to "real" DateTime types we can make it more stable. Let's just hope we don't open a can of worms with timezones and all the other things that suddenly start to matter.

ogrinfo -sql 'SELECT "t_datetime" FROM "geolife_beijing" LIMIT 3' geolife-beijing.sqlite
INFO: Open of `geolife-beijing.sqlite'
      using driver `SQLite' successful.

Layer name: SELECT
Geometry: None
Feature Count: 3
Layer SRS WKT:
(unknown)
t_datetime: String (0.0)
OGRFeature(SELECT):0
  t_datetime (String) = 2008-11-11 00:00:00

OGRFeature(SELECT):1
  t_datetime (String) = 2008-11-11 00:00:01

OGRFeature(SELECT):2
  t_datetime (String) = 2008-11-11 00:00:01

grinfo -sql 'SELECT MIN("t_datetime") FROM "geolife_beijing"' geolife-beijing.sqlite              
INFO: Open of `geolife-beijing.sqlite'
      using driver `SQLite' successful.

Layer name: SELECT
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
MIN("t_datetime"): DateTime (0.0)
OGRFeature(SELECT):0
  MIN("t_datetime") (DateTime) = 2008/11/11 00:00:00

[m-kuhn]

This seems to be more tricky than expected:

• ogr reports the field as string (OFTString)
• but does MIN() and probably other queries using datetime with the format change mentioned in this issue
• sqlite itself (without ogr) gives select min(t_datetime) from geolife_beijing; > 2008-11-11 00:00:00 which looks fine

Like that it seems to be something that we can't fix without some changes (or hints) from ogr side

@rouault any idea if there's something that can be done?

[rouault]

Here's the DDL of this table

CREATE TABLE 'geolife_beijing' (   OGC_FID INTEGER PRIMARY KEY, "GEOMETRY" POINT, 'id' VARCHAR(21), 't_datetime' VARCHAR(255), 't_to_datetime' VARCHAR(255), 'oid' INTEGER, "mydate" TEXT);

So it is expected that OGR reports a string for t_datetime as it is typed as VARCHAR.
OGR would recognized it as a DateTime field if the type in the DDL was DATETIME or TIMESTAMP:
https://github.com/OSGeo/gdal/blob/b1c9c12ad373e40b955162b45d704070d4ebf7b0/gdal/ogr/ogrsf_frmts/sqlite/ogrsqlitelayer.cpp#L435

Regarding the behaviour with MIN(), this is a particular trick in the OGR SQLite driver: since there's no way (at least easy way) to recover the data type of the column passed to MIN(), the driver has a heuristics to try to recover it:
https://github.com/OSGeo/gdal/blob/b1c9c12ad373e40b955162b45d704070d4ebf7b0/gdal/ogr/ogrsf_frmts/sqlite/ogrsqlitelayer.cpp#L446

[m-kuhn]

@anitagraser so this means the easiest solution would be to change the datatype to DATETIME or TIMESTAMP

@rouault do you see anything that can be done to get more stability here in the future? Like

• automatically determining the data type
• specifying the expected data type in the query
• others?

[nirvn]

I'm pretty sure we can close this issue here. The specific datetime filtering issue raised here isn't problem under the new temporal framework

[gioman]

I'm pretty sure we can close this issue here. The specific datetime filtering issue raised here isn't problem under the new temporal framework

@anitagraser @nirvn can we then?

[github-actions]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale".
If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue.
In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue.
If there is no further activity on this issue, it will be closed in a week.