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

Happy SQL-ing!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.