#2707 closed defect (fixed)
OGR SQL should support date and time data types in the ORDER BY clause
Reported by: | jjr8 | Owned by: | warmerdam |
---|---|---|---|
Priority: | normal | Milestone: | 1.6.0 |
Component: | OGR_SF | Version: | svn-trunk |
Severity: | normal | Keywords: | ogr sql date |
Cc: |
Description
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.
Attachments (1)
Change History (5)
by , 15 years ago
comment:1 by , 15 years ago
Keywords: | ogr sql date added |
---|---|
Milestone: | 1.6.0 → 1.6.1 |
Resolution: | → fixed |
Status: | new → closed |
comment:4 by , 15 years ago
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 >>>
Shapefile used in this demonstration.