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?
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
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) ;
cast(col as interval hour to minute).