[SOLVED] PRESTO: Calculating Date Difference Excluding Days: Precision in Months and Handling Greater Than 1 Month Condition

PRESTO: Calculating Date Difference Excluding Days: Precision in Months and Handling Greater Than 1 Month Condition

Question: I'm facing an issue while trying to calculate the difference between two dates in months, excluding the days. Here's the scenario:

WITH dates AS (
    SELECT CAST('2000-03-01 00:00:00' AS TIMESTAMP) AS start_date,
           CAST('2000-04-02 00:00:00' AS TIMESTAMP) AS end_date
),
calculated_age AS (
    SELECT DATE_DIFF('month', start_date, end_date) AS month_diff
    FROM dates
)
SELECT *
FROM calculated_age
WHERE month_diff > 1;

In the above scenario, the actual date difference is more than 1 month and 1 day. However, the DATE_DIFF function in Presto calculates the difference only in whole months, discarding the days. As a result, it doesn't consider the difference to be greater than 1 month.

I would like to know how I can handle such scenarios where I need to calculate the date difference in months with precision and perform a condition like "greater than 1 month" accurately. If DATE_DIFF is not suitable, what alternative query or calculation can be used to address this issue?

Solution

You could turn it around and formulate a query for "is the end date larger than the start date + 1 month?" using the date_add function

WITH dates (start_date, end_date) AS (
    VALUES (TIMESTAMP '2000-03-01 00:00:00', TIMESTAMP '2000-04-02 00:00:00')
)
SELECT *
WHERE end_date > date_add('month', 1, start_date)
FROM dates