CODEWITHSUNDEEP
sqlpostgresqldatetime
Paul Taylor
Paul Taylor

Reputation: 13190

Convert time in the form mm:ss into seconds in Postgres

Using Postgres 9.3.5

My table has duration data in the as a text field stored as minutes:seconds, 

i.e 4:52

I want the value as total seconds (i.e 4 x 60) + 52 = 292

But table also contains values such as

3:34:21 (3 hours, 34  minutes and 21 seconds)
21 (21 seconds)

How can I write SQL to correct calculate duration in seconds for all these cases.

Update

select (case when duration like '%:%:%'
             then extract(epoch from duration::time)
             else extract(epoch from ('00:' || duration) ::time)
 end
)as seconds
from discogs.track t1
;

So I have this but unfortunately some values are not quite valid causing it to fail 

00:70:01

How do I either ignore such values or convert them (i.e 70 x 60 + 1)

Upvotes: 0

Views: 2203

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can convert the value to time and extract the time component. You do need to take the variable format into account:

select (case when t like '%:%:%'
             then extract(epoch from t::time)
             else extract(epoch from ('00:' || t) ::time)
        end) as seconds

You added a new format. You can do:

select (case when t like '%:%:%'
             then extract(epoch from t::time)
             when t like '%:%'
             then extract(epoch from ('00:' || t) ::time)
             else t::int
        end) as seconds

Upvotes: 0

S-Man
S-Man

Reputation: 23666

http://sqlfiddle.com/#!17/9eecb/19379

I expand %:% text to %:%:% texts and then simply split on delimiter ';'. So I am getting integer values which can be used in calculations.

SELECT 
    orig_duration,
    hours_in_seconds + minutes_in_seconds + seconds as seconds 
FROM (
    SELECT  
        orig_duration,
        (split_part(duration, ':', 1))::int * 60 * 60 as hours_in_seconds,
        (split_part(duration, ':', 2))::int * 60 as minutes_in_seconds,
        (split_part(duration, ':', 3))::int as seconds
    FROM (
        SELECT
            duration as orig_duration,
            case when duration like '%:%:%' then duration else '00:' || duration end as duration
        FROM (
            SELECT unnest(ARRAY['70:01','3:34:21','4:52']::text[]) as duration
        )s
    )s
)s

Result:

orig_duration  seconds  
-------------  -------  
70:01          4201     
3:34:21        12861    
4:52           292

Upvotes: 1

Related Questions