[SOLVED] How do I change a time column to an integer column in PostgreSQL with Rails?

How do I change a time column to an integer column in PostgreSQL with Rails?

I have a column named duration in a table named time_entries in a PostgreSQL database connected to a new Rails app. It is currently formatted to be time data, but I want it to be an integer. (Specifically, I'm going for a smallint column because it will be an amount of minutes not exceeding one day i.e. 1440.)

First, I tried:

change_column :time_entries, :duration, :smallint, limit: 2

But I got the following error:

PG::DatatypeMismatch: ERROR:  column "duration" cannot be cast automatically to type smallint
HINT:  You might need to specify "USING duration::smallint".

Then, after looking at this post and this post, I tried the following migration:

change_column :time_entries, :duration, 'integer USING CAST(duration AS integer)'
change_column :time_entries, :duration, :smallint, limit: 2

But the first line returned the following error:

PG::CannotCoerce: ERROR:  cannot cast type time without time zone to integer

How do I get it to convert? Time zone is irrelevant because it actually signifies a duration of time. I'm a Rails novice and know absolutely nothing about raw SQL code.

Solution

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.