2

I am trying to select the latest date by expression in QGIS.

I tried to use this code "Date"= maximum("Date"). But nothing was selected.

I wonder what is the right expression code to select the latest date in general?

The date is formatted as follows for example: 28-4-2018

And could I select the latest date for a specific "ID"?

0
2

Not at all an elegant solution but it works. For those who are still glued to QGIS 2.18.

Let's assume there is a vector layer with the following attribute table.

input

So, the main idea is to select the latest date by expression. It could be feasible by means of the expression that must be pasted in 'Select feature using an expression'. And then press Select.

concat(right("Date",4),'-', regexp_replace(regexp_replace("Date",
       concat(regexp_substr(regexp_replace("Date", 
       right("Date",5),''),'(\\d+)'),'-'),''), right("Date",5),''), '-', 
       regexp_substr(regexp_replace("Date", right("Date",5),''), 
       '(\\d+)'))
=
maximum(concat(right("Date",4),'-', 
       regexp_replace(regexp_replace("Date", 
       concat(regexp_substr(regexp_replace("Date",right("Date",5),''),  
       '(\\d+)'),'-'),''), right("Date",5),''), '-', 
       regexp_substr(regexp_replace( "Date",right("Date",5),''), 
       '(\\d+)')))

As a result, it will select the required feature, see image below

output1


It will work vice verse as well. For selecting the oldest date use this expression

concat(right("Date",4),'-', regexp_replace(regexp_replace("Date",
       concat(regexp_substr(regexp_replace("Date", 
       right("Date",5),''),'(\\d+)'),'-'),''), right("Date",5),''), '-', 
       regexp_substr(regexp_replace("Date", right("Date",5),''), 
       '(\\d+)'))
=
minimum(concat(right("Date",4),'-', 
       regexp_replace(regexp_replace("Date", 
       concat(regexp_substr(regexp_replace("Date",right("Date",5),''),  
       '(\\d+)'),'-'),''), right("Date",5),''), '-', 
       regexp_substr(regexp_replace( "Date",right("Date",5),''), 
       '(\\d+)')))

As a result, it will select the required feature, see image below

output2

2

The issue is, that this is not a "date" format. QGIS uses the international date format which looks like this: yyyy-MM-dd. See ISO 8601 on Wikipedia.

Luckily you can re-format your string to a date using QGIS expressions and then select the maximum of it. Use this expression:

maximum(
    to_date(
        to_string(
            array_get(string_to_array( "date", '-'),2) 
            || '-' || 
            if(length(to_string(array_get(string_to_array( "date", '-'),1)))=1,'0' || to_string(array_get(string_to_array( "date", '-'),1)),to_string(array_get(string_to_array( "date", '-'),1)))
            || '-' || 
            if(length(to_string(array_get(string_to_array( "date", '-'),0)))=1,'0' || to_string(array_get(string_to_array( "date", '-'),0)),to_string(array_get(string_to_array( "date", '-'),0))) 
        )
    )
)

This expression generates an array of your string separated by - signs. (Thanks to @PieterB for pointing out this elegant solution). So array 0 represents the day, array 1 the month and array 2 the year. If string length of day or month is only 1, then it adds a leading 0 to it. The string then gets concatenated and transformed into QGIS date format. Now the maximum can be selected.

Unfortunately you can not combine maximum() function with AND operator because maximum() can not be converted to boolean. So maximum() AND "id" = 1 wont work.

2
  • Nice solution. But I think it is only valid for QGIS 3.x, is not it?
    – Taras
    Sep 28 '19 at 15:45
  • Yes it only works in QGIS 3. But since now QGIS 3.4 replaced 2.18 as LTR this should be ok. In QGIS 2.18 the extraction of day, month and year needs to be done another way.
    – MrXsquared
    Sep 28 '19 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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