Postgresql time as Duration

I need time as duration in postgresql

Eg:- Current time 3/16/2020 13:23:0000

table column entry by 3/15/2020 12:23:0000

so value i need to get is ‘ 1 day 1 hour ago’

if its like this 3/16/2020 13:20:0000 it should be 3 minutes ago like that

Answer

You can do formatting in SQL with something like:

select * from tt;
          x          
---------------------
 2020-02-16 13:30:41
(1 row)

select current_timestamp;
       current_timestamp       
-------------------------------
 2020-03-16 09:38:24.267299+01
(1 row)

select 
 case when dd > 0 then dd || ' days ' else ' ' end 
 ||
 case when hh > 0 then hh || ' hours ' else ' ' end
 ||
 case when mi > 0 then mi || ' minutes ' else ' ' end 
 || 
 'ago' as when
from
(
 select 
  extract(day from (current_timestamp - x)) as dd,
  extract(hour from (current_timestamp - x)) as hh,
  extract(minute from (current_timestamp - x)) as mi 
  from tt
) as t;
              when              
--------------------------------
 28 days 20 hours 7 minutes ago
(1 row)

You can create a stored function for that:

create or replace function format_timestamp(timestamp) returns text
as
$$
select 
 case when dd > 0 then dd || ' days ' else ' ' end 
 ||
 case when hh > 0 then hh || ' hours ' else ' ' end
 ||
 case when mi > 0 then mi || ' minutes ' else ' ' end 
 || 
 'ago' as when
from
(
 select 
  extract(day from (current_timestamp - $1)) as dd,
  extract(hour from (current_timestamp - $1)) as hh,
  extract(minute from (current_timestamp -$1 )) as mi 
) as t;
$$
language sql;