This KBA covers possible date syntax guidelines when making SQL queries for Advanced Reporting.
NB: The AI assistant in Advanced Reporting is not yet sufficiently trained to output accurate time syntax for all dynamic scenarios. As such, time syntax should be taken from either here, past queries, Presto documentation, or the User Guide.
Presto time documentation: https://prestodb.io/docs/current/functions/datetime.html
User Guide content:https://docs.fenergox.com/user-guides/dashboards-&-reporting/sql-user-guide
For basic time filter logic, the following logic from the User Guide can be implemented:
SELECT * FROM journey
WHERE from_iso8601_timestamp(started) >= date('2022-06-01')
AND from_iso8601_timestamp(started) <= date('2022-08-31')
SELECT * FROM journey
WHERE from_iso8601_timestamp(completed) >= date('2022-06-01')
However, using dynamic time filters is often more useful/applicable. The below is an example of correct syntax that could be used for a dynamic date query. This would then not need to be edited every time the date needs changing.
SELECT
j.id,
j.name,
date(from_iso8601_timestamp(j.started)) as "Journey start date" ,
date_add('day', -7, current_date) as "last week date",
date_add('day', 1, current_date) as "today",
j.started,
j.completed
FROM
journey j
WHERE
date(from_iso8601_timestamp(j.started)) >= date_add('day', -7, current_date) and
date(from_iso8601_timestamp(j.started)) < date_add('day', 1, current_date)