I have a Postgresql column that is a varchar that is saving a string that looks like '2 years, 5 months' or '3 months' or '9 years, 0 months'. I can't convert the column to an interval because it's going to break a bunch of existing functionality so what I am trying to do is cast that varchar into an interval in the select statement. I can't figure out how to do this, I was thinking I first need to format it into a timestamp then turn that into an interval? But haven't been able to find any info about this specific case.Thanks for the help!

Edit 1: a_horse_with_no_name's solution worked but I was getting an error previously because of the empty rows so I wrote a case like so to fix it

    duration = ''
    interval '0'
  • Is it always 'years & month' and 'month', or there are other formats?
    – McNets
    Oct 5 '21 at 15:19
  • @McNets It is either 'years, months', 'months', 'empty' Oct 5 '21 at 15:34

If the values all follow the syntax you have shown, you can simply cast it to an interval:

select '2 years, 5 months'::interval

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.