2

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

case
  when
    duration = ''
  then
    interval '0'
  else
    duration::interval
end
thesofaking
  • 27
  • 4

1 Answers1

4

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

select '2 years, 5 months'::interval