0

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.

4
  • Can you add a dbfiddle.uk/?rdbms=postgres_13 that demonstrates the problem? Commented Jul 18, 2021 at 5:48
  • 1
    Prepending the string with 00: seems like a good solution. What's wrong with that?
    – user1822
    Commented Jul 18, 2021 at 5:50
  • I cannot rely on the fact that it will be always formatted MI:SS and that MI will be 0-60. @a_horse_with_no_name I will try add a dbfiddle which illustrates my scenario.
    – Antonio L.
    Commented Jul 18, 2021 at 18:44
  • @Lennart please, find the attached db-fiddle in my (edited) post.
    – Antonio L.
    Commented Jul 18, 2021 at 21:55

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.