Wednesday, July 6, 2011

Greenplum / PostgreSQL Function of the Day: age

How old is my customer?  How old is that order?  How old is our product widget?

When doing customer profiling and segmentation I often want to find a customer's age.  This is also useful for calculating the age of anything.  Luckily, PostgreSQL makes this easy and it's available in Greenplum they have an age function.

The age(date) function will operate on dates through timestamps and return age in years all the way through to years, days, hours, minutes, and seconds.

For example, my sister was born on 7/4/1980.  If her date of birth was in my family table under date_of_birth I could calculate her age :

select age(date_of_birth) from family;

And the db would return 31.

If I wanted to ask, how long have I owned this asset, I could query the db:

select age(created_at) from assets where id = 11; 

And it would calculate the age from this minute as:

age_of_asset                        created_at
3 years 10 mons 14 days 14:59:15    2007-08-22 09:00:45