7

In QGIS I'm trying to filter a specific year out of my field dates (type = QDate). It should not be so hard, still I'm unable to make it work.

I've tried for instance:

  • year("surveys_date") = 2022
  • year("surveys_date") IN 2022

and played around with the '..', "...".

Datasource is a postgreSQL database.

enter image description here

enter image description here

9
  • 1
    Can you please show your data field? Are you certain that it is the QDate type? Because your expression year("field") = 2022 is fully valid !
    – Taras
    Dec 7, 2022 at 9:03
  • 1
    @Taras. I've made screen shots, but I don't know how to share these. Yes, I've double checked the data type (QDate). year("FIELD" = 2022), give me the error: An error occurred when executing the query. The data provider said: ERROR: function year(date) does not exist LINE 1: ...seals_monitoring"."vw_flightdata_epsg28992" WHERE year("surv... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
    – Jessica
    Dec 7, 2022 at 14:10
  • 1
    @Jessica, you can paste a screenshot in your question body, whilst editing, and a dialog confirming to add an image will appear.
    – Matt
    Dec 7, 2022 at 14:26
  • 1
    Obviously you want to use the provider feature filter. As said in my previous comment we need to know the fileformat of the datasource because the correct syntax depends on this detail.
    – MrXsquared
    Dec 7, 2022 at 14:33
  • 1
    Ah sorry, and yes the source is a postgreSQL database
    – Jessica
    Dec 7, 2022 at 15:28

4 Answers 4

5

Since the syntax in the provider feature filter depends on the datasource and your datasource is a PostgreSQL database, in your case you need use this SQL syntax:

extract(year from surveys_date) = 2022

From the docs:

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).

1
  • 1
    Yes! This works! Thank you
    – Jessica
    Dec 8, 2022 at 9:49
2

Filter

"date" >= '01-01-1990' AND "date" <= '30-12-1990'

I'd normally filter dates like this if I need to, date is the qdate field name

Rule based symbology

If you're wanting to visually tell qdates apart rather than filtering

Data

Data

Expressions

Expressions

Outcome

Outcome

2

This should work:

"field" LIKE '2022%'

Where field is the name of your date field.

% is a wildcard representing any characters after 2022.

1

First we need to check what is the data type of the field by right clicking on the layer and "Properties". Then go to "Fields" and and you can see there each column's data type. If it's not Date type then you can change using the "Refactor tool"

Then you can filter using the expression:

"FIELD" = '2022'

UPDATE

A different option is the following:

  1. Right click and select "Properties"

  2. Choose "Fields" and then press the fourth button. "Field Calculator"

  3. Tick the "create virtual field" and give a name like 'filter' and then in the Expression sheet, type in the blank space the name of the column that you want to be filtered by ("Date_of_example") then the operator (> , < , = , ... etc.) and the value. Like this : "Field_name" != 2022 this will create a field with values 1 or 0 for True and False accordingly

  4. on the right side of the UI of QGIS you can find the Processing Toolbox and we will use the "Extract by attribute" under the vector selection category

  5. choose the input layer, the selection attribute will be this column we created, the filter, and then choose the operator (=) and finally set the value of 1 or 0 if you want to keep or dump the other.

all this will create a new layer with only the filtered data

4
  • 1
    How can this work? It implies that you have a string field containing only the year. Question tells that its a date in date format.
    – MrXsquared
    Dec 7, 2022 at 12:08
  • Thank you for your response. Data type is Qdate. When typing: "FIELD" = '2022' it gives the error ERROR: invalid input syntax for type date: "2022"
    – Jessica
    Dec 7, 2022 at 13:28
  • @MrXsquared i post and update
    – TheGame
    Dec 7, 2022 at 23:24
  • Nice that you offer an update, but it suffers the same flawed logic as your original approach, only with a lot more steps. "field_name" != 2022 will return all records because "field_name" is a QDate field, in which no records can possibly equate to 2022.
    – Matt
    Dec 8, 2022 at 6:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.