asked on

Postgresql Interval Query

I am outputting a table that has a user's response with the period of time that has elapsed next to it

John Smith | Yes (15 mins ago)
Bob Jones | No ( 12 days ago)
Sally Thomas | Yes (1 month ago)

I have a table that holds the last updated time, but am having trouble getting the elapsed time. I know that I can get the interval doing:
SELECT NOW() - last_updated
FROM ...

And I know that I can use the EXTRACT or DATE_PART functions to get the value that I need, but I want it to round to the nearest unit such as seconds, minutes, hours, days, weeks, months, years

I would prefer to not use PHP to do the calculations. Any thoughts?

Postgresql 8.1.8
PHP 5.2.1

MG

earth man2

create or replace function nice_interval( in dt interval ) returns interval as $$
declare
seconds int := extract( SECOND from dt );
days int := extract( DAY from dt );
months int := extract( MONTH from dt );
minutes int := extract( MINUTE from dt );
hours int := extract( HOUR from dt );
begin
if ( months = 0 ) then
if ( days = 0 ) then
if hours != 0 then
return hours * interval '1 hour';
elsif minutes != 0 then
return minutes * interval '1 minute';
elsif seconds = 0 then
return dt;
else
return seconds * interval '1 second';
end if;
else
return days * interval '1 day';
end if;
else
return months * interval '1 month';
end if;
end;
$$ language plpgsql;

earth man2

create or replace function nice_interval( in dt interval ) returns interval as $$
declare
seconds int := cast ( extract( SECOND from dt ) as int );
days int := extract( DAY from dt );
months int := extract( MONTH from dt );
minutes int := extract( MINUTE from dt );
hours int := extract( HOUR from dt );
begin
if ( months = 0 ) then
if ( days = 0 ) then
if hours != 0 then
return hours * interval '1 hour';
elsif minutes != 0 then
return minutes * interval '1 minute';
elsif seconds = 0 then
return dt;
else
return seconds * interval '1 second';
end if;
else
return days * interval '1 day';
end if;
else
return months * interval '1 month';
end if;
end;
$$ language plpgsql;

ASKER CERTIFIED SOLUTION

earth man2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mpginsburg

ASKER

Thanks earthman 2. I ended up using AGE() within a CASE statement which is working out well, but I do like you solution so i'll give you the points. Thanks again

MG