Filter layer to show features from -180 to -90 days from today

Question

I want to filter a polygon layer (in a esri file geodatabase) to show features from 180 to 90 days in the past from today (ca 2020-02 to 2020-05). The expression I try with is working in "Select by expression":
("ANDRING_DA" > (now() -  to_interval('180 days'))) AND ("ANDRING_DA" < (now() -  to_interval('90 days')))

But not when right-clicking - filter:

Why, and how can I fix it?

Jakob · Accepted Answer

You can do it with a Virtual Layer in QGIS. It works on a shapefile, so it must also works on a FileGeodatabase since a virtual layers uses a generic SQLite syntax.
select * 
from mytable 
where ANDRING_DA > date('now', '-180 day') AND ANDRING_DA < date('now', '-90 day')

In general it is pretty dirty using a FileGeodatabase in QGIS, compaired to other of the dataformats available.
More on time functions in SQLite: https://www.sqlite.org/lang_datefunc.html

nr_aus · Answer

Im just reading the QGIS documentation and found this
https://docs.qgis.org/3.10/en/docs/user_manual/working_with_vector/vector_properties.html?highlight=query%20builder#query-builder
To quote one section
*"The filter is made at the data provider (OGR, PostgreSQL, MSSQL…) level. So the syntax depends on the data provider (DateTime is for instance not supported for the ESRI Shapefile format). The complete expression:"
Another example shows how to build the query on a geopackage.
So im thinking that maybe the issue has something to do with the fact that you are using a FileGeodatabase.
To test this theory out, you could potentially export your data out to a Geopackage, and then try running the query from the filter there. There is probably a lot more OGR Support for geopackages than FileGeodatabases.

Taras · Answer

Another solution by means of "Function Editor" via "Select Features by Expression"

Use the following function
import datetime
from qgis.core import *
from qgis.gui import *

@qgsfunction(args='auto', group='Custom')
def seldate(start, stop, feature, parent):
    base = datetime.datetime.today()
    date_list = [(base - datetime.timedelta(days=x)).strftime("%d.%m.%Y") for x in range(start, stop)]
    return date_list

This function will give you a list:
['02.05.2020', '01.05.2020', '30.04.2020', '29.04.2020', '28.04.2020', '27.04.2020', '26.04.2020', '25.04.2020', '24.04.2020', '23.04.2020', '22.04.2020', '21.04.2020', '20.04.2020', '19.04.2020', '18.04.2020', '17.04.2020', '16.04.2020', '15.04.2020', '14.04.2020', '13.04.2020', '12.04.2020', '11.04.2020', '10.04.2020', '09.04.2020', '08.04.2020', '07.04.2020', '06.04.2020', '05.04.2020', '04.04.2020', '03.04.2020', '02.04.2020', '01.04.2020', '31.03.2020', '30.03.2020', '29.03.2020', '28.03.2020', '27.03.2020', '26.03.2020', '25.03.2020', '24.03.2020', '23.03.2020', '22.03.2020', '21.03.2020', '20.03.2020', '19.03.2020', '18.03.2020', '17.03.2020', '16.03.2020', '15.03.2020', '14.03.2020', '13.03.2020', '12.03.2020', '11.03.2020', '10.03.2020', '09.03.2020', '08.03.2020', '07.03.2020', '06.03.2020', '05.03.2020', '04.03.2020', '03.03.2020', '02.03.2020', '01.03.2020', '29.02.2020', '28.02.2020', '27.02.2020', '26.02.2020', '25.02.2020', '24.02.2020', '23.02.2020', '22.02.2020', '21.02.2020', '20.02.2020', '19.02.2020', '18.02.2020', '17.02.2020', '16.02.2020', '15.02.2020', '14.02.2020', '13.02.2020', '12.02.2020', '11.02.2020', '10.02.2020', '09.02.2020', '08.02.2020', '07.02.2020', '06.02.2020', '05.02.2020', '04.02.2020', '03.02.2020']

Or if you prefer sorted then:
['03.02.2020', '04.02.2020', '05.02.2020', '06.02.2020', '07.02.2020', '08.02.2020', '09.02.2020', '10.02.2020', '11.02.2020', '12.02.2020', '13.02.2020', '14.02.2020', '15.02.2020', '16.02.2020', '17.02.2020', '18.02.2020', '19.02.2020', '20.02.2020', '21.02.2020', '22.02.2020', '23.02.2020', '24.02.2020', '25.02.2020', '26.02.2020', '27.02.2020', '28.02.2020', '29.02.2020', '01.03.2020', '02.03.2020', '03.03.2020', '04.03.2020', '05.03.2020', '06.03.2020', '07.03.2020', '08.03.2020', '09.03.2020', '10.03.2020', '11.03.2020', '12.03.2020', '13.03.2020', '14.03.2020', '15.03.2020', '16.03.2020', '17.03.2020', '18.03.2020', '19.03.2020', '20.03.2020', '21.03.2020', '22.03.2020', '23.03.2020', '24.03.2020', '25.03.2020', '26.03.2020', '27.03.2020', '28.03.2020', '29.03.2020', '30.03.2020', '31.03.2020', '01.04.2020', '02.04.2020', '03.04.2020', '04.04.2020', '05.04.2020', '06.04.2020', '07.04.2020', '08.04.2020', '09.04.2020', '10.04.2020', '11.04.2020', '12.04.2020', '13.04.2020', '14.04.2020', '15.04.2020', '16.04.2020', '17.04.2020', '18.04.2020', '19.04.2020', '20.04.2020', '21.04.2020', '22.04.2020', '23.04.2020', '24.04.2020', '25.04.2020', '26.04.2020', '27.04.2020', '28.04.2020', '29.04.2020', '30.04.2020', '01.05.2020', '02.05.2020']

And afterwards paste this array_contains(seldate(90,180), "ANDRING_DA")
into "Expression"-tab

Filter layer to show features from -180 to -90 days from today

3 Answers

Add your own answers!

Ask a Question