Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Support for PostgreSQL `interval` type in Rails 4. Although ActiveRecord supports `interval` by default, it turns it into a string (and tells Postgres the column is type string, too). This means you don't get any proper interval goodness. By sticking this code into an initialiser, ActiveRecord will create proper `interval` column types in Postgr…
#
# This will force ActiveRecord to create proper `interval` column types in PostgreSQL
#
# def change
# add_column :leases, :period, :interval
# end
#
# This applies to a generated `schema.rb` file too.
#
# No special OID type is applied to an `interval` type. Rails will treat it as a string, although
# Postgres will reject any invalid interval.
#
# Lease.create name: 'Example', period: '1 year 2 months'
# ...
# Lease.find_by_name('Example')
# => #<Lease name: "Example", period: "1 year 2 mons">
#
# By default, Postgres will output intervals as a string, but you can have it output in other formats like
# ISO8601, which you could use to work with intervals in your application
#
# You can run functions on intervals (and use them in calculations). For example, if you just want to get part
# of an interval:
#
# Lease.select( %{ *, extract('month' from "period")::integer AS period } )
# => #<Lease name: "Example", period: 2>
#
# To get the number of seconds in an interval:
#
# Lease.select( %{ *, extract(epoch from "period")::integer AS period } )
# => #<Lease name: "Example", period: 36741600>
#
# http://www.postgresql.org/docs/9.1/static/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE
#
# add a native DB type of :interval
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES[:interval] = { name: 'interval' }
# add the interval type to the simplified_type list. because this method is a case statement
# we can't inject anything into it, so we create an alias around it so calls to it will call
# our aliased method first, which (if appropriate) will return our type, otherwise passing
# it along to the original unaliased method (which has the normal case statement)
ActiveRecord::ConnectionAdapters::PostgreSQLColumn.class_eval do
define_method("simplified_type_with_interval") do |field_type|
if field_type == 'interval'
:interval
else
send("simplified_type_without_interval", field_type)
end
end
alias_method_chain :simplified_type, 'interval'
end
# add a table definition for migrations, so rails will create 'interval' columns
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::TableDefinition.class_eval do
define_method('interval') do |*args|
options = args.extract_options!
column(args[0], 'interval', options)
end
end
# make sure activerecord treats :intervals as 'text'. This won't provide any help with
# dealing with them, but we can handle that ourselves
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID.alias_type 'interval', 'text'
@vollnhals
Copy link

vollnhals commented Nov 17, 2017

Also see https://gist.github.com/vollnhals/a7d2ce1c077ae2289056afdf7bba094a for an implementation for Rails 5.1

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment