Search code examples
sqlamazon-athenaprestotrino

Using date_diff to calculate diffence between a date column and current_date, displayed in years/months/days

I'm currently trying to create a column for how old an account is in Athena, I have the date column the account was created and want the column to display the account_age as years/months/days

    CAST(DATE_DIFF('year', cu.reg_date, current_date) as varchar)||' years '||
 CAST(DATE_DIFF('month', cu.reg_date, current_date) AS VARCHAR)||' months '||
 CAST(DATE_DIFF('day', cu.reg_date, current_date) -
               (COALESCE(DATE_DIFF('month', cu.reg_date, CURRENT_DATE), 1) * 30)
              AS VARCHAR)||' days'as account_age

the code above brings back years then total months and the days is off. The years seems ok but I don't want total months, just the months when full years have been taken away.

Solution

  • Months are easy, just use the remainder of the division by 12:

    CAST(DATE_DIFF('month', cu.reg_date, current_date) % 12 AS VARCHAR)||' months '

    Days will be harder - you can try adding total passed months to the starting date and then calculate the diff in days:

    CAST(date_diff('day', date_add('month', date_diff('month', cu.reg_date, current_date), cu.reg_date), current_date) AS VARCHAR)||' days'