Using date_add () with date_trunc() in presto

Question

Does anyone know what the below query will return?

"date_add"('month', -1, "date_trunc"('month', "date_add"('day', -1, current_date)))

Assuming that current date is 5 Feb 2022, then my guess is that:

"date_add"('day', -1, current_date) will return 4 Feb 2022 "date_trunc"('month', "date_add"('day', -1, current_date)) will return 1 Feb 2022

Finally, "date_add"('month', -1, "date_trunc"('month', "date_add"('day', -1, current_date))) will return 1 Jan 2022

Answer 1

This transformation returns first day of previous month if initial date is not the first day of the month. For the first day it returns first day of two month ago

select current_date                                               as initial_date,
       "date_add"('day', -1, current_date)                        as second_date, --initial minus 1 day
       "date_trunc"('month', "date_add"('day', -1, current_date)) as third_date,  --second_date truncated to -01
       "date_add"('month', -1, "date_trunc"('month', "date_add"('day', -1, current_date))) as final_result --third date - 1 month

Result:

initial_date  second_date  third_date  final_result 
2022-02-09    2022-02-08   2022-02-01  2022-01-01 --1st of 1 mth back

Test with 1st day of the month:

select date '2022-02-01'                                               as initial_date,
       "date_add"('day', -1, date '2022-02-01')                        as second_date, --initial minus 1 day
       "date_trunc"('month', "date_add"('day', -1, date '2022-02-01')) as third_date,  --second_date truncated to -01
       "date_add"('month', -1, "date_trunc"('month', "date_add"('day', -1, date '2022-02-01'))) as final_result --third date - 1 month

Result:

initial_date  second_date  third_date  final_result 
2022-02-01    2022-01-31   2022-01-01  2021-12-01 --this is 2 mth back