Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Enables PostgreSQL interval datatype support (as ActiveSupport::Duration) in Ruby on Rails from 4.1 to 6.0
# Enables PostgreSQL interval datatype support (as ActiveSupport::Duration) in Ruby on Rails 4.1.
# Based on https://gist.github.com/clarkdave/6529610
require 'active_support/duration'
# 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
# add a table definition for migrations, so rails will create 'interval' columns
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::Table.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'
module ActiveRecord
module ConnectionAdapters
class PostgreSQLAdapter < AbstractAdapter
module OID # :nodoc:
class Interval < ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::Type # :nodoc:
# Converts PostgreSQL interval value (in +postgres+ format) to ActiveSupport::Duration
def type_cast(value)
return value if value.is_a?(::ActiveSupport::Duration)
time = ::Time.now
if value.kind_of?(::Numeric)
return ::ActiveSupport::Duration.new(time.advance(seconds: value) - time, seconds: value)
end
regex = / # Matches postgrs format: -1 year -2 mons +3 days -04:05:06
(?:(?<years>[\+\-]?\d+)\syear[s]?)?\s* # year part, like +3 years+
(?:(?<months>[\+\-]?\d+)\smon[s]?)?\s* # month part, like +2 mons+
(?:(?<days>[\+\-]?\d+)\sday[s]?)?\s* # day part, like +5 days+
(?:
(?<timesign>[\+\-])?
(?<hours>\d+):(?<minutes>\d+)(?::(?<seconds>\d+))?
)? # time part, like -00:00:00
/x
results = regex.match(value)
parts = {}
%i(years months days).each do |param|
next unless results[param]
parts[param] = results[param].to_i
end
%i(minutes seconds).each do |param|
next unless results[param]
parts[param] = "#{results[:timesign]}#{results[param]}".to_i
end
# As hours isn't part of Duration, convert it to seconds
if results[:hours]
parts[:minutes] ||= 0
parts[:minutes] += "#{results[:timesign]}#{results[:hours]}".to_i * 60
end
::ActiveSupport::Duration.new(time.advance(parts) - time, parts)
end
end
end
end
end
end
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID.register_type 'interval', ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::Interval.new
module ActiveRecord
module ConnectionAdapters
module PostgreSQL
module ColumnMethods
def interval(name, options = {})
column(name, :interval, options)
end
end
end
end
end
module ActiveRecord
module ConnectionAdapters
class PostgreSQLAdapter < AbstractAdapter
module Quoting
alias_method :type_cast_without_interval, :type_cast
# Converts ActiveSupport::Duration to PostgreSQL interval value (in +Tradition PostgreSQL+ format)
def type_cast(value, column, array_member = false)
return super(value, column) unless column
case value
when ::ActiveSupport::Duration
if 'interval' == column.sql_type
value.parts.
reduce(::Hash.new(0)) { |h,(l,r)| h[l] += r; h }.
sort_by {|unit, _ | [:years, :months, :days, :minutes, :seconds].index(unit)}.
map {|unit, val| "#{val} #{val == 1 ? unit.to_s.chop : unit.to_s}"}.
join ' '
else
super(value, column)
end
else
type_cast_without_interval(value, column, array_member)
end
end
end
end
end
end
module ActiveSupport
class Duration < ProxyObject
def to_time
hours = value.to_i / 3600
minutes = (value.to_i % 3600) / 60
seconds = ((value.to_i % 3600) % 60)
'%02d:%02d:%02d' % [hours, minutes, seconds]
end
def iso8601(n=nil)
# First, trying to normalize duration parts
parts = ::Hash.new(0)
self.parts.each {|k,v| parts[k] += v }
if parts[:seconds] >= 60
parts[:hours] += parts[:seconds].to_i / 3600
parts[:minutes] += (parts[:seconds].to_i % 3600) / 60
parts[:seconds] = parts[:seconds] % 60
end
if parts[:minutes] >= 60
parts[:hours] += parts[:minutes] / 60
parts[:minutes] = parts[:minutes] % 60
end
if parts[:hours] >= 24
parts[:days] += parts[:hours] / 24
parts[:hours] = parts[:hours] % 24
end
# Build ISO 8601 string parts
years = "#{parts[:years]}Y" if parts[:years].nonzero?
months = "#{parts[:months]}M" if parts[:months].nonzero?
days = "#{parts[:days]}D" if parts[:days].nonzero?
date = "#{years}#{months}#{days}"
hours = "#{parts[:hours]}H" if parts[:hours].nonzero?
minutes = "#{parts[:minutes]}M" if parts[:minutes].nonzero?
if parts[:seconds].nonzero?
sf = parts[:seconds].is_a?(::Float) ? '0.0f' : 'd'
seconds = "#{sprintf(n ? "%0.0#{n}f" : "%#{sf}", parts[:seconds])}S"
end
time = "T#{hours}#{minutes}#{seconds}" if hours || minutes || seconds
"P#{date}#{time}"
end
alias_method :to_s, :iso8601
end
end
# Enables PostgreSQL interval datatype support (as ActiveSupport::Duration) in Ruby on Rails 4.2.
# This initializer is extracted from next pull requests:
# * https://github.com/rails/rails/pull/16917
# * https://github.com/rails/rails/pull/16919
require 'active_support/duration'
module ActiveSupport
class Duration
def inspect #:nodoc:
parts.
reduce(::Hash.new(0)) { |h,(l,r)| h[l] += r; h }.
sort_by {|unit, _ | [:years, :months, :weeks, :days, :hours, :minutes, :seconds].index(unit)}.
map {|unit, val| "#{val} #{val == 1 ? unit.to_s.chop : unit.to_s}"}.
to_sentence(:locale => :en)
end
# Parses a string formatted according to ISO 8601 Duration into the hash .
#
# See http://en.wikipedia.org/wiki/ISO_8601#Durations
# Parts of code are taken from ISO8601 gem by Arnau Siches (@arnau).
# This parser isn't so strict and allows negative parts to be present in pattern.
class ISO8601DurationParser
attr_reader :parts
class ParsingError < ::StandardError; end
def initialize(iso8601duration)
match = iso8601duration.match(/^
(?<sign>\+|-)?
P(?:
(?:
(?:(?<years>-?\d+(?:[,.]\d+)?)Y)?
(?:(?<months>-?\d+(?:[.,]\d+)?)M)?
(?:(?<days>-?\d+(?:[.,]\d+)?)D)?
(?<time>T
(?:(?<hours>-?\d+(?:[.,]\d+)?)H)?
(?:(?<minutes>-?\d+(?:[.,]\d+)?)M)?
(?:(?<seconds>-?\d+(?:[.,]\d+)?)S)?
)?
) |
(?<weeks>-?\d+(?:[.,]\d+)?W)
) # Duration
$/x) || raise(ParsingError.new("Invalid ISO 8601 duration: #{iso8601duration}"))
sign = match[:sign] == '-' ? -1 : 1
@parts = match.names.zip(match.captures).reject{|_k,v| v.nil? }.map do |k, v|
value = /\d+[\.,]\d+/ =~ v ? v.sub(',', '.').to_f : v.to_i
[ k.to_sym, sign * value ]
end
@parts = ::Hash[parts].slice(:years, :months, :weeks, :days, :hours, :minutes, :seconds)
# Validate that is not empty duration or time part is empty if 'T' marker present
if parts.empty? || (match[:time].present? && match[:time][1..-1].empty?)
raise ParsingError.new("Invalid ISO 8601 duration: #{iso8601duration} (empty duration or empty time part)")
end
# Validate fractions (standart allows only last part to be fractional)
fractions = parts.values.reject(&:zero?).select { |a| (a % 1) != 0 }
unless fractions.empty? || (fractions.size == 1 && fractions.last == parts.values.reject(&:zero?).last)
raise ParsingError.new("Invalid ISO 8601 duration: #{iso8601duration} (only last part can be fractional)")
end
end
end
# Creates a new Duration from string formatted according to ISO 8601 Duration.
#
# See http://en.wikipedia.org/wiki/ISO_8601#Durations
# This method allows negative parts to be present in pattern.
# If invalid string is provided, it will raise +ActiveSupport::Duration::ISO8601DurationParser::ParsingError+.
def self.parse!(iso8601duration)
parts = ISO8601DurationParser.new(iso8601duration).parts
time = ::Time.now
new(time.advance(parts) - time, parts)
end
# Creates a new Duration from string formatted according to ISO 8601 Duration.
#
# See http://en.wikipedia.org/wiki/ISO_8601#Durations
# This method allows negative parts to be present in pattern.
# If invalid string is provided, nil will be returned.
def self.parse(iso8601duration)
parse!(iso8601duration)
rescue ISO8601DurationParser::ParsingError
nil
end
# Build ISO 8601 Duration string for this duration.
# The +precision+ parameter can be used to limit seconds' precision of duration.
def iso8601(precision=nil)
output, sign = 'P', ''
parts = normalized_parts
# If all parts are negative - let's output negative duration
if parts.values.compact.all?{|v| v < 0 }
sign = '-'
parts = parts.inject({}) {|p,(k,v)| p[k] = -v; p }
end
# Building output string
output << "#{parts[:years]}Y" if parts[:years]
output << "#{parts[:months]}M" if parts[:months]
output << "#{parts[:weeks]}W" if parts[:weeks]
output << "#{parts[:days]}D" if parts[:days]
time = ''
time << "#{parts[:hours]}H" if parts[:hours]
time << "#{parts[:minutes]}M" if parts[:minutes]
if parts[:seconds]
time << "#{sprintf(precision ? "%0.0#{precision}f" : '%g', parts[:seconds])}S"
end
output << "T#{time}" if time.present?
"#{sign}#{output}"
end
# Return duration's parts summarized (as they can become repetitive due to addition, etc)
# Also removes zero parts as not significant
def normalized_parts
parts = self.parts.inject(::Hash.new(0)) do |p,(k,v)|
p[k] += v unless v.zero?
p
end
parts.default = nil
parts
end
end
end
module ActiveRecord
module ConnectionAdapters
module PostgreSQL
module OID # :nodoc:
class Interval < Type::Value # :nodoc:
def type
:interval
end
def type_cast_from_database(value)
if value.kind_of? ::String
::ActiveSupport::Duration.parse!(value)
else
super
end
end
def type_cast_from_user(value)
type_cast_from_database(value)
rescue ::ActiveSupport::Duration::ISO8601DurationParser::ParsingError
value # Allow user to supply raw string values in another formats supported by PostgreSQL
end
def type_cast_for_database(value)
case value
when ::ActiveSupport::Duration
value.iso8601(self.precision)
when ::Numeric
time = ::Time.now
duration = ::ActiveSupport::Duration.new(time.advance(seconds: value) - time, seconds: value)
duration.iso8601(self.precision)
else
super
end
end
end
end
end
end
end
module ActiveRecord
module ConnectionAdapters
module PostgreSQL
module ColumnMethods
def interval(name, options = {})
column(name, :interval, options)
end
end
end
end
end
require 'active_record/connection_adapters/postgresql/schema_statements'
module ActiveRecord
module ConnectionAdapters
module PostgreSQL
module SchemaStatements
alias_method :type_to_sql_without_interval, :type_to_sql
def type_to_sql(type, limit = nil, precision = nil, scale = nil)
case type.to_s
when 'interval'
return super unless precision
case precision
when 0..6; "interval(#{precision})"
else raise(ActiveRecordError, "No interval type has precision of #{precision}. The allowed range of precision is from 0 to 6")
end
else
type_to_sql_without_interval(type, limit, precision, scale)
end
end
end
end
end
end
require 'active_record/connection_adapters/postgresql_adapter'
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES[:interval] = { name: 'interval'}
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.class_eval do
define_method :initialize_type_map_with_interval do |m|
initialize_type_map_without_interval(m)
m.register_type 'interval' do |_, _, sql_type|
precision = extract_precision(sql_type)
::ActiveRecord::ConnectionAdapters::PostgreSQL::OID::Interval.new(precision: precision)
end
end
alias_method_chain :initialize_type_map, :interval
define_method :configure_connection_with_interval do
configure_connection_without_interval
execute('SET intervalstyle = iso_8601', 'SCHEMA')
end
alias_method_chain :configure_connection, :interval
end
# This file is taken from https://gist.github.com/vollnhals/a7d2ce1c077ae2289056afdf7bba094a
# PostgreSQL interval data type support from https://github.com/rails/rails/pull/16919
# Place this file to config/initializers
# activerecord/lib/active_record/connection_adapters/postgresql/oid/interval.rb
require "active_support/duration"
module ActiveRecord
module ConnectionAdapters
module PostgreSQL
module OID # :nodoc:
class Interval < Type::Value # :nodoc:
def type
:interval
end
def cast_value(value)
case value
when ::ActiveSupport::Duration
value
when ::String
begin
::ActiveSupport::Duration.parse(value)
rescue ::ActiveSupport::Duration::ISO8601Parser::ParsingError
nil
end
else
super
end
end
def serialize(value)
case value
when ::ActiveSupport::Duration
value.iso8601(precision: self.precision)
when ::Numeric
# Sometimes operations on Times returns just float number of seconds so we need to handle that.
# Example: Time.current - (Time.current + 1.hour) # => -3600.000001776 (Float)
value.seconds.iso8601(precision: self.precision)
else
super
end
end
def type_cast_for_schema(value)
serialize(value).inspect
end
end
end
end
end
end
# activerecord/lib/active_record/connection_adapters/postgresql/schema_definitions.rb
module ActiveRecord
module ConnectionAdapters
module PostgreSQL
module SchemaDefinitions
def interval(name, options = {})
column(name, :interval, options)
end
end
end
end
end
# activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
require 'active_record/connection_adapters/postgresql/schema_statements'
module SchemaStatementsWithInterval
def type_to_sql(type, limit: nil, precision: nil, scale: nil, array: nil, **)
case type.to_s
when 'interval'
case precision
when nil; "interval"
when 0..6; "interval(#{precision})"
else raise(ActiveRecordError, "No interval type has precision of #{precision}. The allowed range of precision is from 0 to 6")
end
else
super
end
end
end
ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements.send(:prepend, SchemaStatementsWithInterval)
# activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
require 'active_record/connection_adapters/postgresql_adapter'
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES[:interval] = { name: 'interval'}
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.class_eval do
alias_method :initialize_type_map_without_interval, :initialize_type_map
define_method :initialize_type_map do |m|
initialize_type_map_without_interval(m)
m.register_type 'interval' do |_, _, sql_type|
precision = extract_precision(sql_type)
::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::Interval.new(precision: precision)
end
end
alias_method :configure_connection_without_interval, :configure_connection
define_method :configure_connection do
configure_connection_without_interval
execute('SET intervalstyle = iso_8601', 'SCHEMA')
end
ActiveRecord::Type.register(:interval, ::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::Interval, adapter: :postgresql)
end
pg_interval_support_6_0.rb
# frozen_string_literal: true
# PostgreSQL interval data type support from https://github.com/rails/rails/pull/16919
# Works with both Rails 5.2 and 6.0
# Place this file to config/initializers/
require "active_support/duration"
# activerecord/lib/active_record/connection_adapters/postgresql/oid/interval.rb
module ActiveRecord
module ConnectionAdapters
module PostgreSQL
module OID # :nodoc:
class Interval < Type::Value # :nodoc:
def type
:interval
end
def cast_value(value)
case value
when ::ActiveSupport::Duration
value
when ::String
begin
::ActiveSupport::Duration.parse(value)
rescue ::ActiveSupport::Duration::ISO8601Parser::ParsingError
nil
end
else
super
end
end
def serialize(value)
case value
when ::ActiveSupport::Duration
value.iso8601(precision: self.precision)
when ::Numeric
# Sometimes operations on Times returns just float number of seconds so we need to handle that.
# Example: Time.current - (Time.current + 1.hour) # => -3600.000001776 (Float)
value.seconds.iso8601(precision: self.precision)
else
super
end
end
def type_cast_for_schema(value)
serialize(value).inspect
end
end
end
end
end
end
# activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
require 'active_record/connection_adapters/postgresql_adapter'
PostgreSQLAdapterWithInterval = Module.new do
def initialize_type_map(m = type_map)
super
m.register_type "interval" do |*args, sql_type|
precision = extract_precision(sql_type)
::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::Interval.new(precision: precision)
end
end
def configure_connection
super
execute('SET intervalstyle = iso_8601', 'SCHEMA')
end
ActiveRecord::Type.register(:interval, ::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::Interval, adapter: :postgresql)
end
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.prepend(PostgreSQLAdapterWithInterval)
# activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
require 'active_record/connection_adapters/postgresql/schema_statements'
module SchemaStatementsWithInterval
def type_to_sql(type, limit: nil, precision: nil, scale: nil, array: nil, **)
case type.to_s
when 'interval'
case precision
when nil; "interval"
when 0..6; "interval(#{precision})"
else raise(ActiveRecordError, "No interval type has precision of #{precision}. The allowed range of precision is from 0 to 6")
end
else
super
end
end
end
ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements.prepend(SchemaStatementsWithInterval)
@einSelbst

This comment has been minimized.

Copy link

einSelbst commented Oct 29, 2017

To bad the PR is still not merged :( . I tried to adapt this for rails 5.1 but couldn't get it to work. The moment my model is saved the field is always set to 0. If at some point you feel motivated to add an example for rails 5.1 it would be much appreciated! Thanks for you work on this!

@vollnhals

This comment has been minimized.

Copy link

vollnhals commented Nov 17, 2017

@einSelbst we created an implementation for Rails 5.1. You could try that. https://gist.github.com/vollnhals/a7d2ce1c077ae2289056afdf7bba094a

@Envek

This comment has been minimized.

Copy link
Owner Author

Envek commented Feb 1, 2020

@vollnhals, I have cherry-picked your implementation for Rails 5.1 back here. Also have added version for 5.2 and 6.0.

@magnaleo

This comment has been minimized.

Copy link

magnaleo commented Jul 16, 2020

Hey! Thanks! I've found out that using https://github.com/henrypoydar/chronic_duration is better inside the cast_value memeber function if the input is a string. I used it to make it work with the RailsAdmin interface.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.