OGR SQL should support date and time data types in the ORDER BY clause

[jjr8]

Given that ​http://www.gdal.org/ogr/ogr_sql.html makes no mention that date and time data types are not supported in the ORDER BY clause, I'm assuming you intend them to be supported. Please adjust ticket fields as appropriate.

Here's a demo using the shapefile driver that shows rows are returned in the wrong order when a date field is used in the ORDER BY clause. The demo uses Python bindings, as described in Repro Steps 1 and 2 of #2688.

>>> from osgeo import ogr
>>> ds = ogr.Open('C:\\Test4\\test.shp')
>>> layer = ds.ExecuteSQL('SELECT * FROM test ORDER BY FDate ASC')
>>> feat = layer.GetNextFeature()
>>> while feat is not None:
...     print feat.GetFieldAsString(feat.GetFieldIndex('FDate'))
...     feat = layer.GetNextFeature()
...
2005/10/25
2003/10/24
2003/10/25
2003/10/25
1985/10/25
2005/10/25
2003/10/24
2003/10/25
2003/10/25
1985/10/25
>>>

To fix this, I believe, at minimum, that cases must be added to OGRGenSQLResultsLayer::Compare() in ogr_gensql.cpp for handling date and time data types. But OGRGenSQLResultsLayer is a pretty complicated bit of code, so I hesitate to suggest a specific fix.

Although I tested with GDAL 1.5.0, OGRGenSQLResultsLayer::Compare() showed no support for date or time data types in the 1.5.3 tag or the trunk, so I'm assuming the problem still exists.

I have not yet tested with OGR drivers other than shape. It may be that many database-oriented drivers such as PGeo allow the underlying database engine to process the ORDER BY clause. If so, this problem is probably restricted to the non-database-oriented drivers.

Being able to order rows by date and time is pretty important for our application. We have lots of points that we have to process in date/time order (e.g. telemetry from animals fitted with satellite tags. coordinates from fishing vessels fitted with tracking systems, etc.). We would really love to see this fixed for 1.6.0 if possible. Thanks for looking at it.

[jjr8]

Shapefile used in this demonstration.

[Even Rouault]

Fixed in trunk (1.7.0dev...) in r15843 and in branches/1.6 in r15844

We are now in RC1 for 1.6.0, so this may come in 1.6.1.

[Even Rouault]

Will go in 1.6.0 as an RC2 will be produced

[jjr8]

That's great news! Thanks for the quick fix.

[jjr8]

I can confirm that this appears to be fixed in GDAL 1.6.0 with Python 2.5 bindings on win32. In the output below, the rows appear to be returned in the correct order. In the original example above, they were returned unsorted.

Python 2.5.2 (r252:60911, Feb 21 2008, 13:11:45) [MSC v.1310 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> from osgeo import ogr
>>> ds = ogr.Open('C:\\Test4\\test.shp')
>>> layer = ds.ExecuteSQL('SELECT * FROM test ORDER BY FDate ASC')
>>> feat = layer.GetNextFeature()
>>> while feat is not None:
...     print feat.GetFieldAsString(feat.GetFieldIndex('FDate'))
...     feat = layer.GetNextFeature()
...
1985/10/25
1985/10/25
2003/10/24
2003/10/24
2003/10/25
2003/10/25
2003/10/25
2003/10/25
2005/10/25
2005/10/25
>>> del feat, layer
>>> layer = ds.ExecuteSQL('SELECT * FROM test ORDER BY FDate DESC')
>>> feat = layer.GetNextFeature()
>>> while feat is not None:
...     print feat.GetFieldAsString(feat.GetFieldIndex('FDate'))
...     feat = layer.GetNextFeature()
...
2005/10/25
2005/10/25
2003/10/25
2003/10/25
2003/10/25
2003/10/25
2003/10/24
2003/10/24
1985/10/25
1985/10/25
>>>