Postgres Pro
Downloads
Home   >   mailing lists

Thread: format return of "age" to hh:mm

format return of "age" to hh:mm

From
David Gauthier
Date:
Hi:

How does one reformat the output of the "age" function to always be in terms of hours:mins.

E.g. 

dvdb=> select age('2020-03-05 01:40:32-05','2020-03-01 21:56:05-05');
       age      
-----------------
 3 days 03:44:27
(1 row)

I want...

"75:44"

I'm not married to "age"  If there's a better way to do this that's fine too.

Thanks in advance !  

Re: format return of "age" to hh:mm

From
Ray O'Donnell
Date:
On 05/03/2020 15:50, David Gauthier wrote:
> Hi:
> 
> How does one reformat the output of the "age" function to always be in
> terms of hours:mins.

Hi there,

age() returns an interval, so without having tried it I'm guessing you
could use to_char() to format it whatever way you want.

Ray.

-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

Re: format return of "age" to hh:mm

From
Andrei Zhidenkov
Date:
However, you cannot use to_char() to display the count of days for a given interval. In this case, if your interval is
largerthan 24 hours, you might use extract(epoch from <interval>) and perform the conversion manually. 

> On 5. Mar 2020, at 17:07, Ray O'Donnell <ray@rodonnell.ie> wrote:
>
> On 05/03/2020 15:50, David Gauthier wrote:
>> Hi:
>>
>> How does one reformat the output of the "age" function to always be in
>> terms of hours:mins.
>
> Hi there,
>
> age() returns an interval, so without having tried it I'm guessing you
> could use to_char() to format it whatever way you want.
>
> Ray.
>
> --
> Raymond O'Donnell // Galway // Ireland
> ray@rodonnell.ie
>
>

Re: format return of "age" to hh:mm

From
"David G. Johnston"
Date:
On Thu, Mar 5, 2020 at 8:50 AM David Gauthier <davegauthierpg@gmail.com> wrote: 
Hi:

How does one reformat the output of the "age" function to always be in terms of hours:mins.



Custom function.

Use justify_hours(interval) to normalize the input in terms of days
Use extract(field from interval) to get the components, including days
Multiply the days result by 24, add it to the hours result
Deal with fractional hours
Combine and return

There is no justify_minutes function unfortunately which, if implemented to the behavior of justify_hours, would do what you are looking for.  You basically want to write one, though I suspect in SQL instead of C.

David J.

Re: format return of "age" to hh:mm

From
Adrian Klaver
Date:
On 3/5/20 7:50 AM, David Gauthier wrote:
> Hi:
> 
> How does one reformat the output of the "age" function to always be in 
> terms of hours:mins.
> 
> E.g.
> 
> dvdb=> select age('2020-03-05 01:40:32-05','2020-03-01 21:56:05-05');
>         age
> -----------------
>   3 days 03:44:27
> (1 row)
> 
> I want...
> 
> "75:44"
> 
> I'm not married to "age"  If there's a better way to do this that's fine 
> too.

Not sure it's better, but it will give you idea of what needs to be done:


SELECT
     floor(
         extract(
             epoch FROM ('2020-03-05 01:40:32-05'::timestamptz - 
'2020-03-01 21:56:05-05'::timestamptz))
             / 3600)::varchar || ':' ||
     ((mod(
           extract(
               epoch FROM ('2020-03-05 01:40:32-05'::timestamptz - 
'2020-03-01 21:56:05-05'::timestamptz))::numeric,
  3600::numeric) / 60)::int)::varchar;

?column?
----------
  75:44
(1 row)

> 
> Thanks in advance !


-- 
Adrian Klaver
adrian.klaver@aklaver.com