The blog has moved to http://jessehouse.com/ ... Many google searches point here so I am leaving it operational, but there will be no new posts.

Wednesday, November 9, 2011

Postgres: get the number of days in an interval

-- seconds / 60 = minutes / 60 = hours / 24 = days
select (((EXTRACT(EPOCH FROM INTERVAL '2 years') / 60) / 60) / 24)::integer as number_of_days 

good resource - http://stackoverflow.com/questions/952493/how-do-i-convert-an-interval-into-a-number-of-hours-with-postgres

2 comments:

Matej Klobusnik said...

If you are calculating the days from two dates or timestamps I would skip using interval and use this one:

select t.date1 :: date - t.date2 :: date as days_between, * from person t;

House 9 said...

yeah in my case the data was stored in the db as an INTERVAL