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
Last activity: Jul 19, 2021, 12:33 AM