How do I change a time column to an integer column in PostgreSQL with Rails?
You can extract the epoch (number of seconds) from a TIME value pretty easily:
SELECT EXTRACT(EPOCH FROM '01:00:00'::TIME)::INT-- Returns 3600 In your case, I would suspect you could do this:
change_column :time_entries, :duration, 'integer USING EXTRACT(EPOCH FROM duration)::INT'
You need to provide an expression to do the actual conversion with a USING clause:
ALTER TABLE time_entries ALTER duration TYPE int2 USING EXTRACT(EPOCH FROM duration)::int2;Be aware that any value exceeding the range of a smallint will raise an exception that aborts the whole transaction.
dbfiddle here
Related:
Thanks to the information provided in the other answers, I did the following in a Rails migration:
change_column :time_entries, :duration, 'SMALLINT USING EXTRACT(EPOCH FROM duration)/60::SMALLINT'This converted the column into SMALLINT numbers representing the amount of minutes. I just wanted to include the final solution here since I modified the code from the other answers a little bit.