How to use Postgres interval data type with Rails 6

Question

Based on other answers here I thought I could do this:

rails generate model Availability

class CreateAvailabilities < ActiveRecord::Migration[6.0]
  def change
    create_table :availabilities do |t|
      t.references :user,      null: false, foreign_key: true
      t.datetime   :starts_at, null: false
      t.interval   :duration,  null: false
      t.timestamps             null: false
    end
  end
end

Availability.rb

class Availability < ApplicationRecord
  attribute :duration, :duration 
  belongs_to :user
end

config/initializers/duration_type.rb

class DurationType < ActiveRecord::Type::String
  def cast(value)
    return value if value.blank? || value.is_a?(ActiveSupport::Duration)
    ActiveSupport::Duration.parse(value)
  end
  def serialize(duration)
    duration ? duration.iso8601 : nil
  end
end
ActiveRecord::Type.register(:duration, DurationType)

rails console

Loading development environment (Rails 6.0.3.2)
> a = Availability.create(user: User.first, starts_at: Time.now, duration: 10.minutes)
  User Load (0.7ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT $1  [["LIMIT", 1]]
   (0.2ms)  BEGIN
  Availability Create (1.7ms)  INSERT INTO "availabilities" ("user_id", "starts_at", "duration", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["user_id", 1], ["starts_at", "2020-08-09 18:50:37.732198"], ["duration", "PT10M"], ["created_at", "2020-08-09 18:50:37.747158"], ["updated_at", "2020-08-09 18:50:37.747158"]]
   (1.0ms)  COMMIT
> a.duration
=> 10 minutes

I thought I was home free but I get this error when I try this

> Availability.first.duration
  Availability Load (0.6ms)  SELECT "availabilities".* FROM "availabilities" ORDER BY "availabilities"."id" ASC LIMIT $1  [["LIMIT", 1]]
Traceback (most recent call last):
        3: from (irb):5
        2: from (irb):6:in `rescue in irb_binding'
        1: from config/initializers/duration_type.rb:5:in `cast'
ActiveSupport::Duration::ISO8601Parser::ParsingError (Invalid ISO 8601 duration: "00:10:00")

It seems to be creating the Postgres database entry correctly but I'm confused why it's not able to read it back

Answer 1

Might be that you need to change how the duration is persisted in the database. It looks like it’s stored like 00:10:00. But the parser is expecting the string to be in ISO8601 format (PT10M).

class ChangeIntervalStyleInDatabase < ActiveRecord::Migration[6.0]
  def self.up
    execute "ALTER DATABASE \"#{connection.current_database}\" SET intervalstyle = 'iso_8601'"
  end

  def self.down
    execute "ALTER DATABASE \"#{connection.current_database}\" SET intervalstyle = 'postgres'"
  end
end

Rails 6.1+

Since my original answers, Rails 6.1 has been released adding support for PostgreSQL interval datatype. This eliminates the need for a custom type that casts and serializes values.

• Rails 6.1 Changelog
• Commit