Error trying to use gdal to open a dataset in PostGIS with the following code:
ds = gdal.Open("PG: dbname=nyc host=localhost user=postgres password=mypassword port=5432 mode=2 schema=public column=rast table=mytable where='collected_date > 2014-04-04 07:00:00'", gdal.GA_ReadOnly)
The log file in PostGIS shows:
2016-04-18 21:09:36 MYT ERROR: syntax error at or near "07" at character 336
2016-04-18 21:09:36 MYT STATEMENT: select srid, nbband, ST_XMin(geom) as xmin, ST_XMax(geom) as xmax, ST_YMin(geom) as ymin, ST_YMax(geom) as ymax, scale_x, scale_y from (select ST_SRID(rast) srid, ST_Extent(rast::geometry) geom, max(ST_NumBands(rast)) nbband, avg(ST_ScaleX(rast)) scale_x, avg(ST_ScaleY(rast)) scale_y from public.cea where collected_date > 2014-04-04 07:00:00 group by ST_SRID(rast)) foo
Apparently there is a syntax error in the date condition (in bold), where it needs to be enclosed with quotations. But I can't seem to be able to insert the quotations at all as they are always removed when the string is parsed.
I guess we can replace the date with mktime function instead, but I'll looking for a solution that can be extended to string datatype as well.