Store time interval in PostgreSQL from Rails

Question

I need to store time inverval in PosgreSQL. Data will be put from Ruby on Rails.

Say, PizzaHut accepts orders from 9:00 to 18:00.

I've created following migration:

class AddOrderTimeAndDeliveryTimeToMerchants < ActiveRecord::Migration
  def self.up
    add_column :merchants, :order_time, :interval
  end

I've read the documentation, and have following code now:

Merchant.create( :delivery_time => "9:00 18:00" )

When i execute it, i get following error message:

PGError: ERROR:  invalid input syntax for type interval: "9:00 18:00"

How to do it correctly ?

Answer 1

I don't think an interval is really what you want for that. An interval represents a timespan without any specific end points; for example, you add an interval to an existing time to get another time. You would be better off with two distinct times:

add_column :merchants, :order_from, :time, :null => false
add_column :merchants, :order_to,   :time, :null => false

Then, if for some reason you need to know how many hours they're open for delivery, you can construct an interval by subtracting :order_from from :order_to.

If you really must use an interval, then you'll need to construct a value something like this:

:delivery_time => "interval '11 hour'"

Note how this illustrates that an interval is not a specific time range from A to B, it is just a time range of some certain length (with no specified end points).