Bug report #17320

Can't filter a table by a date field

Added by Patrick Dunford over 6 years ago. Updated about 5 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Vectors
Affected QGIS version:2.18.13 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:no timely feedback
Crashes QGIS or corrupts data:No Copied to github as #:25218

Description

I have a shapefile whose data table has a field that contains dates and is shown in the layer properties as being QDateTime type.

If I try to filter the table by entering a date constant the OGR provider keeps throwing error messages and refusing to apply the filter.

There is no documentation anywhere that I can find that documents how to specify a date value to a filter and trying some of the common SQL ones like quote marks or # characters have failed.

History

#1 Updated by Giovanni Manghi over 6 years ago

  • Category changed from GUI to Vectors
  • Status changed from Open to Feedback

Please attach sample data.

#3 Updated by Giovanni Manghi over 6 years ago

  • Operating System deleted (Debian 9.1)
  • Status changed from Feedback to Open
  • Affected QGIS version changed from master to 2.18.13

This (example) works

cast(date as character) = '1938/02/04'

taken from

https://gis.stackexchange.com/a/100050

#4 Updated by Patrick Dunford over 6 years ago

That seems to do the trick. However it is an issue on masters I have tested with, do you have any suggestion it is relevant to a particular version of master?

Can this be added to the official documentation as I did search for it but did not find a solution such as this.. How is this documentation produced.

#5 Updated by Harrissou Santanna over 6 years ago

Patrick Dunford wrote:

Can this be added to the official documentation as I did search for it but did not find a solution such as this.. How is this documentation produced.

Patrick, the documentation source is at www.github.com/qgis/QGIS-Documentation and, from each html doc page you have a link at the bottom to directly access its source file and propose the changes you want. Guidelines to write docs are accessible at http://docs.qgis.org/testing/en/docs/documentation_guidelines/index.html. And of course all these information are available in the QGIS main website.

That said, there are some information at http://docs.qgis.org/testing/en/docs/user_manual/working_with_vector/expression.html#date-and-time-functions (I don't know if it would have solved your problem, though). I recall that Alexandre made a lot of tests with these field types before writing it. If you wanna complete/improve it, you are of course welcome...

#6 Updated by Giovanni Manghi over 6 years ago

Harrissou Santanna wrote:

Patrick Dunford wrote:

Can this be added to the official documentation as I did search for it but did not find a solution such as this.. How is this documentation produced.

Patrick, the documentation source is at www.github.com/qgis/QGIS-Documentation and, from each html doc page you have a link at the bottom to directly access its source file and propose the changes you want. Guidelines to write docs are accessible at http://docs.qgis.org/testing/en/docs/documentation_guidelines/index.html. And of course all these information are available in the QGIS main website.

That said, there are some information at http://docs.qgis.org/testing/en/docs/user_manual/working_with_vector/expression.html#date-and-time-functions (I don't know if it would have solved your problem, though). I recall that Alexandre made a lot of tests with these field types before writing it. If you wanna complete/improve it, you are of course welcome...

I agree that a patch/contribution to docs would be good. I am anyway wondering if this is not really a bug, in the sense that in case of real date fields the above workaround should be applied by QGIS filter in a transparent way(?). Opinions?

#7 Updated by Harrissou Santanna over 6 years ago

Giovanni Manghi wrote:

I am anyway wondering if this is not really a bug, in the sense that in case of real date fields the above workaround should be applied by QGIS filter in a transparent way(?). Opinions?

Agreed, field type should be enough to QGIS to handle data transparently and do these basic operations.
People should not have to cast it unless they wish to do some other particular process, not easily compatible with the field type.

#8 Updated by Jürgen Fischer over 5 years ago

  • Status changed from Open to Feedback

Please test with QGIS 3.4 - QGIS 2.18 reached it's end of life.

#9 Updated by Giovanni Manghi about 5 years ago

  • Resolution set to no timely feedback
  • Status changed from Feedback to Closed

Also available in: Atom PDF