Date operation in the Query Builder withPostGIS layer

Question

I would like to filter a PostGIS layer by a datetime (QDateTime) field. I only want to show elements newer then 14 days. I tried the following expression in the Quary Builder:

(day(now() - "creation_date")) <= 14 

The same expression is working in the Field Calculator but not in the Query Builder.

Any idea what I did wrong? Using QGIS v3.22.3

Answer 1

day() is a QGIS Expression function, not a SQL function. The proper SQL syntaxe is

(now() - "creation_date") <= '14 days'

or

EXTRACT( DAYS FROM now() - "creation_date") <= 14