Sample Header Ad - 728x90

Postgres cast MI:SS formatted string to "interval minute to second" type

0 votes
0 answers
314 views
I have a data source with the time information being formatted like this: 'MI:SS'. I am trying to coerce that to a native postgres INTERVAL or TIME type so I can leverage the equivalent operators. I have tried the following:
CASE
            WHEN (event."BIDASK" = 'BP') THEN '10:00'
            WHEN (event."MARKERTIME" = '00:-1') OR (event."MARKERTIME" = '') THEN '00:00'
            ELSE event."MARKERTIME"
        END ::interval minute to second
event."MARKERTIME" is a "MM:SS" formatted string. I note however that it gets converted to 'HH:MM' in the casted interval type. ie. for the string "10:00" - I get: hours 10, minutes 00, seconds 00. - I would expect: minutes 10, seconds 00 I am working on postgres 13. Here is a db-fiddle illustrating this casting behaviour: https://www.db-fiddle.com/f/2QqG36bKnjAyjqecBKpDU/2 EDIT: One trick I find it working is to prepend the string with '00:' but I am hoping for a better way to achieve that because I cannot rely on the fact that the MI part of the string will be bounded between 0 and 59.
Asked by Antonio L. (31 rep)
Jul 18, 2021, 04:09 AM
Last activity: Jul 19, 2021, 12:33 AM