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.
00:
seems like a good solution. What's wrong with that?