Using interval in PostgreSQL with Ruby on Rails

Question

I want to save durations (2 days, 5 years, ...) as intervals in PostgreSQL from my Rails application.

Both duration_min and duration_max are values like "2 days" or "5 years", so each of them is an interval by itself:

  def change
    create_table :times do |t|
      t.interval  :duration_min
      t.interval  :duration_max
      t.timestamps
    end
  end

but the DB migration fails when setting the data type to "interval" and rake returns:

undefined method 'interval' for #<ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::TableDefinition:0x007f8615694360>

How do I define the table for it to accept (and understand) an input like "2 days"?

The original migration now works with rails 5.1.3 (probably >= 5.1): However, when reading back it still is treated as string, e.g. for "P1M" or 1.month, the record will hold a string "730:29:06". — Kjell
– Kjell, Commented Aug 22, 2017 at 10:45
FYI, there's a Rails PR to support deserialization: github.com/rails/rails/pull/16919 — Alex
– Alex, Commented Dec 6, 2017 at 20:10

mdesantis · Accepted Answer · 2013-12-02 14:22:34Z

13

You were close:

class CreateExamples < ActiveRecord::Migration
  def change
    create_table :examples do |t|
      t.column :duration_min, :interval
      t.column :duration_max, :interval
      t.timestamps
    end
  end
end

Usage example:

Example.create duration_min: '2 hours', duration_max: '2 days'
#=> #<Example id: 1, duration_min: "2 hours", duration_max: "2 days", created_at: "2013-12-02 14:20:36", updated_at: "2013-12-02 14:20:36">
Example.where(%[TIMESTAMP ? - TIMESTAMP ? BETWEEN "duration_min" AND "duration_max"], DateTime.now, 10.hours.ago)
#=> #<ActiveRecord::Relation [#<Example id: 1, duration_min: "02:00:00", duration_max: "2 days", created_at: "2013-12-02 14:20:36", updated_at: "2013-12-02 14:20:36">]>

edited Dec 2, 2013 at 14:22

answered Dec 2, 2013 at 13:59

mdesantis

8,5574 gold badges35 silver badges64 bronze badges

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

Comments

lion.vollnhals · Accepted Answer · 2017-11-17 15:44:37Z

2

We have an implementation of interval for Rails 5.1 that you can try.

https://gist.github.com/vollnhals/a7d2ce1c077ae2289056afdf7bba094a

answered Nov 17, 2017 at 15:44

lion.vollnhals

1,8021 gold badge12 silver badges5 bronze badges

Collectives™ on Stack Overflow

Using interval in PostgreSQL with Ruby on Rails

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related