Re: [Qgis-user] Filter syntax for date fields

[Patrick Dunford]

Yes. I have read that document. However, the issue isn't the SQL
support, it is recognising a date constant in what is effectively a
WHERE clause, because none of the usual ways of specifying a date
constant (in other forms of SQL) are recognised, nor is it documented
there.
Because you could put a range of dates like 1984-01-01 to 1984-12-31 to
work with a year if the format for inputting a date constant was
available.
On Fri, 2017-10-20 at 10:14 +, Alexandre Neto wrote:
> Hi,
> Vector Layer filtering SQL syntax is limited to the data provider. If
> you are using a shapefile, then the provider is GDAL and you are
> limited to this operands:
> 
> http://www.gdal.org/ogr_sql.html
> If you used Geopackage or Spatialite or PostGIS you would probably
> have more datetime specific operands that might work with your field.
> Alexandre Neto
> 
> 
> A sex, 20/10/2017, 03:30, Patrick Dunford 
> escreveu:
> > I have a shapefile table with 300,000 records of polygons that I
> > want
> > 
> > to filter for display on the map canvas. I want to use the field
> > called
> > 
> > "date" which is defined as a Qdate in the fields list in the
> > 
> > shapefile's properties.
> > 
> > 
> > 
> > The question is how to put in the filter string in the correct
> > syntax
> > 
> > to give me for example all the records that are for the calendar
> > year
> > 
> > of 1984.
> > 
> > ___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Filter syntax for date fields

[Alexandre Neto]

Hi,

Vector Layer filtering SQL syntax is limited to the data provider. If you
are using a shapefile, then the provider is GDAL and you are limited to
this operands:


http://www.gdal.org/ogr_sql.html

If you used Geopackage or Spatialite or PostGIS you would probably have
more datetime specific operands that might work with your field.

Alexandre Neto

A sex, 20/10/2017, 03:30, Patrick Dunford 
escreveu:

> I have a shapefile table with 300,000 records of polygons that I want
> to filter for display on the map canvas. I want to use the field called
> "date" which is defined as a Qdate in the fields list in the
> shapefile's properties.
>
> The question is how to put in the filter string in the correct syntax
> to give me for example all the records that are for the calendar year
> of 1984.
>
> For example
>  year("date") = 1984
> will not work because it doesn't accept the year function from the list
> of functions that are available in other expression editors which allow
> a function list to be used.
>
> In a previous version of this table I had it was actually quite easy
> because the dates were input as a string and the like operator could be
> used. It seems with a date that is actually defined as a date field
> there is no obvious or easy way to input a range of dates (how do you
> put in a date constant) or use a function that will extract part of a
> date from a date field.
>
> ___
> Qgis-user mailing list
> Qgis-user@lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

-- 
Alexandre Neto
-
@AlexNetoGeo
http://sigsemgrilhetas.wordpress.com
http://gisunchained.wordpress.com
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Filter syntax for date fields

[Patrick Dunford]

Probaly as a workaround right now it looks like for my scenario the
simplest q option is to extract the date into separate y/m/d fields
stored as integers and run the filter against these. 

On Fri, 2017-10-20 at 10:25 +0200, Luigi Pirelli wrote:
> This could be related with a similar proble reported in
> https://issues.qgis.org/issues/16657 assigned to 3ndis (Denis) that
> will work on that (master and 2.18) on November during bugfix period.
> The parse is done QDateTime and is hard to match the exact QDateTime
> format. If it match, than the value is correctly used, otherwise the
> current time is used.
> 
> probably we need a more general way to parte datetime shared with
> editor delegate and functions.
> 
> you can do a workaround adding your custom function that instruct
> QDate to parse only Y
> 
> Luigi Pirelli
> 
> *
> *
> * Boundless QGIS Support/Development: lpirelli AT boundlessgeo DOT
> com
> * LinkedIn: https://www.linkedin.com/in/luigipirelli
> * Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirel
> li
> * GitHub: https://github.com/luipir
> * Mastering QGIS 2nd Edition:
> * https://www.packtpub.com/big-data-and-business-intelligence/masteri
> ng-qgis-second-edition
> *
> *
> 
> 
> On 20 October 2017 at 04:30, Patrick Dunford  > wrote:
> > I have a shapefile table with 300,000 records of polygons that I
> > want
> > to filter for display on the map canvas. I want to use the field
> > called
> > "date" which is defined as a Qdate in the fields list in the
> > shapefile's properties.
> > 
> > The question is how to put in the filter string in the correct
> > syntax
> > to give me for example all the records that are for the calendar
> > year
> > of 1984.
> > 
> > For example
> >  year("date") = 1984
> > will not work because it doesn't accept the year function from the
> > list
> > of functions that are available in other expression editors which
> > allow
> > a function list to be used.
> > 
> > In a previous version of this table I had it was actually quite
> > easy
> > because the dates were input as a string and the like operator
> > could be
> > used. It seems with a date that is actually defined as a date field
> > there is no obvious or easy way to input a range of dates (how do
> > you
> > put in a date constant) or use a function that will extract part of
> > a
> > date from a date field.
> > 
> > ___
> > Qgis-user mailing list
> > Qgis-user@lists.osgeo.org
> > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Filter syntax for date fields

[Luigi Pirelli]

This could be related with a similar proble reported in
https://issues.qgis.org/issues/16657 assigned to 3ndis (Denis) that
will work on that (master and 2.18) on November during bugfix period.
The parse is done QDateTime and is hard to match the exact QDateTime
format. If it match, than the value is correctly used, otherwise the
current time is used.

probably we need a more general way to parte datetime shared with
editor delegate and functions.

you can do a workaround adding your custom function that instruct
QDate to parse only Y

Luigi Pirelli

**
* Boundless QGIS Support/Development: lpirelli AT boundlessgeo DOT com
* LinkedIn: https://www.linkedin.com/in/luigipirelli
* Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
* GitHub: https://github.com/luipir
* Mastering QGIS 2nd Edition:
* 
https://www.packtpub.com/big-data-and-business-intelligence/mastering-qgis-second-edition
**


On 20 October 2017 at 04:30, Patrick Dunford  wrote:
> I have a shapefile table with 300,000 records of polygons that I want
> to filter for display on the map canvas. I want to use the field called
> "date" which is defined as a Qdate in the fields list in the
> shapefile's properties.
>
> The question is how to put in the filter string in the correct syntax
> to give me for example all the records that are for the calendar year
> of 1984.
>
> For example
>  year("date") = 1984
> will not work because it doesn't accept the year function from the list
> of functions that are available in other expression editors which allow
> a function list to be used.
>
> In a previous version of this table I had it was actually quite easy
> because the dates were input as a string and the like operator could be
> used. It seems with a date that is actually defined as a date field
> there is no obvious or easy way to input a range of dates (how do you
> put in a date constant) or use a function that will extract part of a
> date from a date field.
>
> ___
> Qgis-user mailing list
> Qgis-user@lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

[Qgis-user] Filter syntax for date fields

[Patrick Dunford]

I have a shapefile table with 300,000 records of polygons that I want
to filter for display on the map canvas. I want to use the field called
"date" which is defined as a Qdate in the fields list in the
shapefile's properties.

The question is how to put in the filter string in the correct syntax
to give me for example all the records that are for the calendar year
of 1984.

For example
 year("date") = 1984
will not work because it doesn't accept the year function from the list
of functions that are available in other expression editors which allow
a function list to be used.

In a previous version of this table I had it was actually quite easy
because the dates were input as a string and the like operator could be
used. It seems with a date that is actually defined as a date field
there is no obvious or easy way to input a range of dates (how do you
put in a date constant) or use a function that will extract part of a
date from a date field.

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user