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

Question

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. Thank you!

Answer 1

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'

Answer 2

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:

• Rails Migrations: tried to change the type of column from string to integer
• Get a timestamp from concatenating day and time columns

Answer 3

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.