SYNTAX_ERROR: line 1:20: Column 'sysdate' cannot be resolved

sysdate is a function. Try sysdate() instead.

Sorry, I was wrong. Athena uses current_date. Try that.

You also might need to use date_diff for subtracting the two dates like in this post: https://stackoverflow.com/questions/58326786/athena-datediff

Please refer to this documentation page for a list of all Athena functions, and specifically to this trino documentation page for the Date & Time Function.

Your Query should read as:

SELECT  avg(date_diff('day',PZD.ORDER_DATE,current_date)) as MY_DATE_DIFF 
FROM PZDTORBK PZD 
WHERE PZD.ORDER_DATE > cast('2021-01-01'as timestamp)

hope this helps