How to filter date values using OGR 2.4.0?

Question

I am trying to replicate a working Oracle SQL query into ogr2ogr, but having issues with a date field where clause.

My query is:

select BLOCK_STATUS_DATE, GEOMETRY
from WHSE_FOREST_TENURE.FTEN_CUT_BLOCK_POLY_SVW
where BLOCK_STATUS_DATE > '01-JUL-18'
and rownum < 9
order by BLOCK_STATUS_DATE asc

This query returns a logical result, with dates that are all newer than July 1, 2018.

05-JUL-19   [MDSYS.SDO_GEOMETRY]
05-JUL-19   [MDSYS.SDO_GEOMETRY]
05-JUL-19   [MDSYS.SDO_GEOMETRY]
18-JUL-19   [MDSYS.SDO_GEOMETRY]
18-NOV-19   [MDSYS.SDO_GEOMETRY]
18-NOV-19   [MDSYS.SDO_GEOMETRY]
18-NOV-19   [MDSYS.SDO_GEOMETRY]

The same query, run through an ogr2ogr command, returns 8 rows with dates ranging from 2002 to 2019. It seems the SQL dialect is getting confused between Oracle SQL and whatever dialect ogr2ogr uses.

For completeness, here is my total ogr2ogr command:

ogr2ogr.exe -a_srs epsg:3005 -f "GPKG" T:\test\RKB_cutblocks.gpkg OCI:USER/PASSWORD@IDWPROD1:no_Table -progress -sql @T:\tempQueryFolder\query.sql -lco IDENTIFIER='layer1' -lco DESCRIPTION='layer1' 

If anyone knows how I can cast the date in my query, or specify a proper SQL dialect to ogr2ogr, or any other solution, please let me know!

Answer 1

In your sql statement:

select BLOCK_STATUS_DATE, GEOMETRY from WHSE_FOREST_TENURE.FTEN_CUT_BLOCK_POLY_SVW where BLOCK_STATUS_DATE > '01-JUL-18' and rownum < 9 order by BLOCK_STATUS_DATE asc

The statement relies on oracle to take care of the conversion of '01-JUL-18' to a date. While this works sometimes you never really know how oracle is going to interpret that string and convert it to a date. You can be more explicit by handling the conversion of the string '01-JUL-18' to a date using the to_date oracle function.

Example: select BLOCK_STATUS_DATE, GEOMETRY from WHSE_FOREST_TENURE.FTEN_CUT_BLOCK_POLY_SVW where BLOCK_STATUS_DATE > to_date('01-JUL-18', 'DD-MON-YY') and rownum < 9 order by BLOCK_STATUS_DATE asc

I just tested this sql with the OGR command you provided and can verify that it returned only data after July 1, 2018. That said the extraction was SLOW!

I'm guessing you probably want records for July 1st so you may want to say >=. You can also get down to the time for that date. See (https://www.techonthenet.com/oracle/functions/to_date.php).

Finally while i'm at it if you want to rid yourself of the dependency of the TNSNAMES config with your oracle client installation you can specify your database connection like this (changed output to shapefile to simplify):

ogr2ogr -a_srs epsg:3005 -f "ESRI Shapefile" out.shp OCI:<user>/<pass>@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<host>)(PORT=<ora port>)))(CONNECT_DATA=(SERVICE_NAME=<service name>))):no_table -progress -sql @./query.sql

Hope this helps.