5

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
LakeHMM
  • 201
  • 1
  • 6
  • 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 Mar 27 '18 at 00: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 Mar 27 '18 at 00:37

3 Answers3

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

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
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'
Nick
  • 7,103
  • 2
  • 21
  • 43
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.

LakeHMM
  • 201
  • 1
  • 6