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)

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!

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

@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.