Athena Query Error - (Column 'day' cannot be resolved)

The error you're encountering, "Column 'day' cannot be resolved," is likely due to a syntax issue in your query. In Athena, the DATEDIFF function uses a slightly different syntax than what you've used.

To resolve this issue, you should modify the DATEDIFF function in your query. Instead of DATEDIFF(DAY, previous_order_date, date_added), use DATEDIFF('day', previous_order_date, date_added).

Here's the corrected query:

CREATE OR REPLACE VIEW avg_day_between_orders AS SELECT name_detail, AVG(DATEDIFF('day', previous_order_date, date_added)) AS avg_day_between_orders FROM ( SELECT t.*, LAG(date_added) OVER (PARTITION BY name_detail ORDER BY date_added) AS previous_order_date FROM brpsalesorderdetails t ) AS with_previous_order GROUP BY name_detail;

This change should resolve the "Column 'day' cannot be resolved" error. The 'day' parameter in DATEDIFF should be a string literal, enclosed in single quotes.

If you continue to experience issues, here are a few additional things to check:

1. Ensure that the column names 'name_detail' and 'date_added' exist in your 'brpsalesorderdetails' table.
2. Verify that 'date_added' is indeed a date or timestamp type column.
3. Check if there are any null values in the 'date_added' column that might be causing issues with the LAG function.

If the error persists after making these changes, you may want to review the Athena query history in the console to see if there are any other syntax or data-related issues that could be causing the problem.
Sources
Troubleshoot issues in Athena - Amazon Athena
View recent queries in the Athena console - Amazon Athena

The "Column 'day' cannot be resolved" error that you are encountering, is likely due to a syntax issue in the query. To resolve this issue, please modify the DATEDIFF function in your query.

Based on the error it suggests that it is trying to find the column with name "day" in your table. This is picking up from first argument of the datediff function. Presto expects a string value to be passed as the first "units" parameter. Please enclose the 'day' parameter in DATEDIFF in single quotes as shown below.



DATEDIFF('day', previous_order_date, date_added) instead of DATEDIFF(DAY, previous_order_date, date_added)

[+] https://prestodb.io/docs/current/functions/datetime.html#date_diff

This error may also occur when the said column is missing from the Athena data source. If your Data source does have that column and the issue still persists, you can open a support case with AWS Athena team with query ID and region name.