Dates are always a pain to manage. SQL is no exception with every database doing their way.
Today I talk about AWS Athena where the date/time documentation is here, not on the AWS website but the presto one. Interesting.
This query return 2020-08-24 and this is today
SELECT current_date AS today_in_iso;
This query return 2020-08-23 and this is yesterday
SELECT current_date - interval '1' day AS yesterday_in_iso;
I love the interval datatype, it reminds me of PostgreSQL. Also no need for a FROM.