Selecting rows in attribute table by date (QGIS)

Question

I want to select rows in an attribute table by date using PYQGIS.

The column is called “Date.” This is my script:

import qgis.utils
from qgis.utils import iface

layer = iface.activeLayer()
layer.selectByExpression('"Date"=\'2021-09-06 20:36:59\'')

The script runs, does not throw an error, and also does not select the desired row. To make sure I wasn’t doing something wrong with function inputs, I tried this on different columns in the same attribute table:

layer.selectByExpression('"Elevation"=\'SG-3\'') 
layer.selectByExpression('"Location"=\'North of B1\'')
layer.selectByExpression('"Maximum Dry Density"=\'138\'')

All of these produced the desired outcome – the rows were selected.

More details in case relevant: Attribute table values were all input using the Mergin Maps field app, then synced to the project using Mergin's plugin. The Date column is the only input that is auto-filled in the app. All other inputs are manual.

I have looked in the PYQGIS Developer Cookbook and various posts here on StackExchange and haven't found a solution. Any advice welcome.

Answer 1

In your use case, QGIS considers it as a string. You need to convert it to datetime format.

Use this:

expression = "Date = to_datetime('2021-09-06 20:36:59', 'yyyy-MM-dd HH:mm:ss')"
layer.selectByExpression(expression)

If you want to be able to select rows by just yyyy-MM-dd, since the type of Date is DateTime, you need to convert into Date using to_date.

expression = "to_date(Date) = to_date('2021-09-06', 'yyyy-MM-dd')"