0

I have Hours,Minute,Second, and days as results in the Postgres query. I want to convert everything into hours.

Example

Row 1 result: 19:53:45
Row 2 result: 1 day 05:41:58

Now I want to convert days into hours like below

   Row 1 result:19:53:45
    Row 2 result: 29:41:58

Can someone help me how to do it in the postgres sql?

user3614760
  • 9
  • 2
  • 7
  • 1
    What data type is that column? –  Sep 02 '20 at 13:28
  • 1
    Does this answer your question? [How do I convert an interval into a number of hours with postgres?](https://stackoverflow.com/questions/952493/how-do-i-convert-an-interval-into-a-number-of-hours-with-postgres) – Mike Organek Sep 02 '20 at 13:29
  • I'd try `cast(col as interval hour to minute)`. – jarlh Sep 02 '20 at 13:38
  • @jarlh: that doesn't change anything: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=5bfe94f0b3b9a3ca07c57da8692dfcc6 –  Sep 02 '20 at 14:00

2 Answers2

1

cast(col as interval hour to minute) should work, according to Standard SQL.

Anyway, this seems to work:

col - extract(day from col) * interval '1'  day  -- remove the days 
    + extract(day from col) * interval '24' hour -- and add back as hours

See fiddle

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • It is working fine for 1 day. my row results may have n days. could you help me to cover all day scenarios? ex 2 days, 10 days, etc. – user3614760 Sep 06 '20 at 14:26
  • It's working for any number of days, at least in my fiddle. What's wrong with it? – dnoeth Sep 06 '20 at 16:27
0

Presumably, you want the result as a string, because times are limited to 24 hours. You can construct it as:

select *,
       (case when ar[1] like '%day%'
             then (split_part(col, ' ', 1)::int * 24 + split_part(ar[1], ' ', 3)::int)::text ||
                  right(col, 6)
             else col
        end)
from (values ('19:53:45'), ('1 day 05:41:58')) v(col) cross join lateral
     regexp_split_to_array(col, ':') a(ar);

You can also do this without a:

select *, 
       (case when col like '%day%'
             then (split_part(col, ' ', 1)::int * 24 + (regexp_match(col, ' ([0-9]+):'))[1]::int)::text ||
                  right(col, 6)
             else col
        end)
from (values ('19:53:45'), ('1 day 05:41:58')) v(col) ;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786