How do I find the difference between two time periods?

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

Remember how to get the current year-month period?

	——————————————-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

view raw

SQL_difference_between_timeperiods_functions_vertica.sql

hosted with ❤ by GitHub

Happy SQL-ing!