How to change time column to integer column in PostgreSQL with Rails?

Question

I have a column with a name durationin a table with a name time_entriesin a PostgreSQL database connected to a new Rails application. It is currently formatted as time data, but I want it to be an integer. (In particular, I am going for a small column, because this will be the number of minutes not exceeding one day, that is, 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

? , . Rails SQL-. !

Answer 1

, , Rails:

change_column :time_entries, :duration, 'SMALLINT USING EXTRACT(EPOCH FROM duration)/60::SMALLINT'

SMALLINT, . , .

Answer 2

USING:

ALTER TABLE time_entries ALTER duration TYPE int2 USING EXTRACT(EPOCH FROM duration)::int2;

, , a smallint, , .

dbfiddle

:

• Rails Migrations:

Answer 3

( ) TIME:

SELECT EXTRACT(EPOCH FROM '01:00:00'::TIME)::INT
-- Returns 3600 

, :

change_column :time_entries, :duration, 'integer USING EXTRACT(EPOCH FROM duration)::INT'