datetimepostgisqgisquery
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
Your observation is correct, The Query Builder does not use the same syntax as the rest of QGIS.
Throughout QGIS, the syntax is based on QGIS Expressions a customized SQL dialect. This is portable between almost all parts of QGIS where you can enter a filter, calculate a value... This syntax is parsed and evaluated directly in QGIS (and parts of it may be sent to the provider sometimes).
The provider feature filter defined via query builder (also often referred to as subset string) for the layer works different in the way that QGIS does not look at it at all. All it does is send it to the provider. In this case the provider is OGR which only supports a subset of the SQL syntax but sometimes it's very powerful, e.g. when accessing a postgres/postgis database.
The easiest way to find out how to reproduce something in python is to run it once in ArcMap using the ArcToolbox tool (or in this case, the Field Calculator), and then choosing Copy Python Snippet from the geoprocessing results.
Run the Field Calculator
Open the Results pane (Geoprocessing > Results)
Find the corresponding tool results, right-click and select Copy As Python Snippet
Now paste that Python snippet into your IDE. This will be the code you want to use in python.
# Replace a layer/table view name with a path to a dataset (which can be a layer file) or create the layer/table view within the script
# The following inputs are layers or table views: "testPoint"
arcpy.CalculateField_management(in_table="testPoint", field="HourField", expression="arcpy.time.ParseDateTimeString( !datefield! ).strftime('%H')", expression_type="PYTHON_9.3", code_block="")
You can also rewrite how this is written in python now you know how it should be structured (I have used your values in this one):
expression = "arcpy.time.ParseDateTimeString(!dtrepor!).strftime('%H')"
arcpy.CalculateField_management(saved_Incidents, "mil_time", expression, "PYTHON_9.3")
Best Answer
day()is a QGIS Expression function, not a SQL function. The proper SQL syntaxe isor