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. I told that the ‘minute to second’ thing would restrict that but that is not the case.
I am working on postgres 13.
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.