title: Convert time in the form mm:ss into seconds in Postgres
body: <p>Using Postgres 9.3.5</p>
  
  <p>My table has duration data in the as a <code>text</code> field stored as minutes:seconds, </p>
  
  <pre><code>i.e 4:52
  </code></pre>
  
  <p>I want the value as total seconds (i.e 4 x 60) + 52 = 292</p>
  
  <p>But table also contains values such as</p>
  
  <pre><code>3:34:21 (3 hours, 34  minutes and 21 seconds)
  21 (21 seconds)
  </code></pre>
  
  <p>How can I write SQL to correct calculate duration in seconds for all these cases.</p>
  
  <p><strong>Update</strong></p>
  
  <pre><code>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
  ;
  </code></pre>
  
  <p>So I have this but unfortunately some values are not quite valid causing it to fail </p>
  
  <pre><code>00:70:01
  </code></pre>
  
  <p>How do I either ignore such values or convert them (i.e 70 x 60 + 1)</p>