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

Question

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)

S-Man · Accepted Answer · 2018-08-16 15:08:16Z

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

.. when I try on small record set but failing when ran over whole database - stackoverflow.com/questions/51897967/…

Gordon Linoff · Accepted Answer · 2018-08-17 01:28:55Z

0

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

edited Aug 17, 2018 at 1:28

answered Aug 16, 2018 at 11:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

6 Comments

Paul Taylor Over a year ago

Thanks but fail because of some invalid data please see updated date how do I resolve this.

Gordon Linoff Over a year ago

@PaulTaylor . . . I think that is pretty easily fixed by adjusting the case expression. This seems rather simpler than the other answer.

Paul Taylor Over a year ago

Thanks Gordon but I didnt really understand what epoch or extract did so I went with a solution I could work into my solution

Gordon Linoff Over a year ago

@PaulTaylor . . . You should learn. They do exactly what you are asking for, which is extracting the total number of seconds from a date/time field.

Paul Taylor Over a year ago

but it wasnt a datetime field it was a string purporting to be a time field but containing non standard values. I learn alot of things but I feel yo are giving me a hard time because I picked someone elses answer over yours.

|

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Linked

Related