I understand Athena uses Presto, however the function last_day_of_month(x) in the documentation doesn't seem to work in AWS Athena.
Is there a function I can use to get the last day of the previous month based on the current date (30 september 2021), last day of previous year (31 december 2021) and last day of the half year (30 June 2022) etc?
I used the below script to do this, however it would be good to know if there's a function I can use or simpler way to run the dates.
SELECT date_trunc('month', current_date) - interval '1' daySELECT date_trunc('year',(date_trunc('month', current_date) - interval '1' day)) - interval '1' daySELECT date_add('month',6, date_trunc('year',(date_trunc('month', current_date) - interval '1' day)) - interval '1' day)Best Answer
First, you need to upgrade your Workgroup to use the Athena engine version 3, which already supports last_day_of_month(x) function.
Athena is based on Presto/Trino, and each version of the Athena engine is based on a different version of the open-source project. You can control the version from the Workgroups menu and even let Athena upgrade the engine automatically for you.
Second, if you want a get the last day of the previous month, the easiest way is to create the first day of the following month and substruct one day from it.
SELECT date '2012-08-01' - interval '1' dayTherefore, if you want the last day of the previous month, and as suggested in the comment, using date_trunc:
SELECT date_trunc('month', current_date ) - interval '1' day--- half year backSELECT date_trunc('month', current_date - interval '6' month) - interval '1' day--- one year backSELECT date_trunc('month', current_date - interval '1' year) - interval '1' day