Apart from the standard datediff() and timestampdiff(), Vertica and MySQL offer a couple of more ways to go about it:
- By using the age functions: age_in_years (current date, start date) and age_in_months (current date, start date)
A key thing to remember about these two is that, unlike datediff (‘years’, start date, current date), they will calculate the months/years between the two dates that have already fully lapsed.
- By using the months_between (current date, start date) function: as this function does not employ a rounding approach, it will return the exact time in decimals between your two dates. This makes it particularly handy for forecasting future inventory levels.
- period_diff (later period, earlier period) calculates the number of months between two year-month periods
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
——————————————-Vertica—————————————————————————————- | |
select | |
age_in_months(Now(), '1988-06-21'), —calculates completed period without current month | |
age_in_years(Now(), '1988-06-21'), —calculates completed period without current month | |
datediff('month', '1988-06-21', Now()) as MonthsBetweenDates, —includes current month in calculation | |
datediff('year', '1988-06-21', Now()) as YearsBetweenDates, —includes current month in calculation | |
months_between (Now(),'2017-01-01') — calculates months between two dates as a float | |
——————————————MySQL———————————————————————————————— | |
select | |
period_diff(201712, 201707) as Past, —months between now and a past date | |
period_diff(201806, 201712) as Future —months between now and a future date | |
Happy SQL-ing!