CODEWITHSUNDEEP
sqldatabasepostgresqldateselect
Proto
Proto

Reputation: 157

postgresql - Format an interval as time with large number of hours

I have an interval that is calculated between events that can be days or hours between them.

Some rows show the desirable

00:15:38.687282

While other give me a worse-to-read

2 days 22:20:33.505085

How can I make the second example return the following?

70:22:33.505085

Thanks for any help.

Upvotes: 2

Views: 813

Answers (3)

Pavel Stehule
Pavel Stehule

Reputation: 45770

Another solution:

select extract(epoch from interval '2 days 22:20:33.505085')/3600 * interval '1h';
┌─────────────────┐
│    ?column?     │
╞═════════════════╡
│ 70:20:33.505085 │
└─────────────────┘
(1 row)

Upvotes: 2

Adrian Klaver
Adrian Klaver

Reputation: 19620

A quick and dirty solution:

SELECT
   extract(days from '2 days 22:20:33.505085'::interval) * interval '24 hour' + '22:20:33.505085'::interval

 ?column?     
-----------------
 70:20:33.505085

If you want to get more involved then that, account for months, years and so on then you will probably need to build a function.

Upvotes: 0

GMB
GMB

Reputation: 222432

You can build a custom representation with extract():

floor(extract(epoch from val) / 60 / 60)
|| ':' || extract(minute from val)
|| ':' || extract(second from val)

Demo on DB Fiddle:

select 
    floor(extract(epoch from val) / 60 / 60)
    || ':' || extract(minute from val)
    || ':' || extract(second from val) display
from (values ('2 days 22:20:33.505085'::interval)) as t(val)

| display         |
| :-------------- |
| 70:20:33.505085 |

Upvotes: 1

Related Questions