Enables PostgreSQL interval datatype support (as ActiveSupport::Duration) in Ruby on Rails from 4.1 to 6.0

pg_interval_support_4_1.rb

# 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

pg_interval_support_4_2.rb

# 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

pg_interval_support_5_1.rb

# 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_5_2.rb

pg_interval_support_6_0.rb

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)

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

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.