PG::Error timezone when changing string column to time data type

Question

I need to change column data type, currently these columns are strings, but I need to change that it would be "time".

Migration file:

def change
  change_column :enquiries, :start_time, :time
  change_column :enquiries, :end_time, :time
end

Error message:

PG::Error: ERROR: column "start_time" cannot be cast automatically to type time without time zone HINT: You might need to specify "USING start_time::time without time zone".

After some searches I found this solution, but it still not working, however I get new error message.

def change
  change_column :enquiries, :start_time, 'time USING CAST(start_time AS time)'
  change_column :enquiries, :end_time, 'time USING CAST(end_time AS time)'
end

PG::Error: ERROR: invalid input syntax for type time: ""

I tried matthewd solution but it still not works.

change_column :enquiries, :start_time, "time USING CAST(NULLIF(start_time, '') AS time)"

PG::Error: ERROR: invalid input syntax for type time: "09.00"

Any help how to solve it?

Answer 1

Basically, this is what I did. There was a lot of start_time and end_time records and these two columns didn't have validation so some records were not correct formation (HH:MM). However, need to refactor this code, but this is what worked for me.

def up
  rename_column :enquiries, :start_time, :start_time_old
  rename_column :enquiries, :end_time, :end_time_old
  add_column :enquiries, :start_time, :time
  add_column :enquiries, :end_time, :time

  Enquiry.reset_column_information
  Enquiry.find_each do |e| 
    unless e.start_time_old.blank?
      if e.start_time_old.include?('.')
        e.update_column(:start_time , e.start_time_old.gsub('.', ':'))
      else
        e.update_column(:start_time , e.start_time_old)
      end
    end
  end

  Enquiry.find_each do |e| 
    unless e.end_time_old.blank?
      if e.end_time_old.include?('.')
        e.update_column(:end_time , e.end_time_old.gsub('.', ':'))
      else
        e.update_column(:end_time , e.end_time_old)
      end
    end
  end

  remove_column :enquiries, :start_time_old
  remove_column :enquiries, :end_time_old
end