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.

The other answers to this question are valid – they'll convert your Postgres TIME field into a Unix epoch, which is the number of seconds from the 1st of January 1970 – but that sounds like it's not what you really want here. You're attempting to convert an absolute time into a delta. To that end, you probably need some glue code in addition to your existing field to change that from an absolute reference in time to a duration. If that sounds right then let me know and I can write an answer to help you in that direction. — Josh McMillan
– Josh McMillan, Commented Mar 27, 2018 at 0:34
@JoshMcMillan - This statement of yours is not true: "they'll convert your Postgres TIME field into a Unix epoch, which is the number of seconds from the 1st of January 1970". Please try out the answers to see for yourself. — Nick
– Nick, Commented Mar 27, 2018 at 0:37

Nick · Accepted Answer · 2018-03-27 00:31:52Z

3

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'

answered Mar 27, 2018 at 0:31

Nick

7,5012 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2023-02-06 16:19:59Z

3

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

answered Mar 27, 2018 at 0:31

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Comments

LakeHMM · Accepted Answer · 2018-03-29 01:26:15Z

1

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.

answered Mar 29, 2018 at 1:26

LakeHMM

2011 silver badge6 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related