CODEWITHSUNDEEP
sqlpostgresqldatetimeintervalsdate-arithmetic
Chris Kessel
Chris Kessel

Reputation: 5865

Convert time to duration

I have raw data in a table where I get a duration in the form of text string "4:30 hrs." What's a good way to parse that and convert into minutes? For example, "4:30 hrs." would parse into the integer 270.

Upvotes: 1

Views: 396

Answers (1)

GMB
GMB

Reputation: 222402

Postgres is flexible enough to turn string '4:30 hrs' to an interval by just casting it.

Then all that is left to do is turn the interval to minutes, which you can do by extracting epoch from it, and dividing the result by 60.

So I would just go:

extract(epoch from '4:30 hrs'::interval) / 60

Demo on DB Fiddle:

select mycol, extract(epoch from mycol::interval) / 60 res
from (values ('04:30 hrs'::text), ('29:40 hrs'::text)) as t(mycol)

mycol     | res 
:-------- | :---
04:30 hrs | 270 
29:40 hrs | 1780

Upvotes: 3

Related Questions