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
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
PHPPostgreSQL
Last Comment
mpginsburg
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;
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
MG
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;