---
title: "Rails 6.1 adds support for PostgreSQL interval data type"
description: "Rails 6.1 adds support for  PostgreSQL interval data type"
canonical_url: "https://www.bigbinary.com/blog/rails-6-1-adds-postgresql-interval-data-type"
markdown_url: "https://www.bigbinary.com/blog/rails-6-1-adds-postgresql-interval-data-type.md"
---

# Rails 6.1 adds support for PostgreSQL interval data type

Rails 6.1 adds support for PostgreSQL interval data type

- Author: Akhil Gautam
- Published: January 26, 2021
- Categories: Rails, Rails 6.1

<br />

### What is PostgreSQL Interval Data Type?

PostgreSQL Interval Data Type allows us to store a duration/period of time in
years, months, days, hours, minutes, seconds, etc. It also allows us to perform
arithmetic operations on that interval.

There are two input formats for interval data. These formats are used to write
interval values.

1. Verbose format:

```ruby
  <quantity> <unit> [<quantity> <unit>...] [<direction>]

  # Examples:
  '2 years ago'
  '12 hours 13 minutes ago'
  '8 years 7 months 2 days 3 hours'
```

- `quantity` can be any number.
- `unit` can be any granular unit of time in plural or singular form like
  days/day, months/month, weeks/week, etc..
- `direction` can be `ago` or an empty string.

2. ISO 8601 formats:

```ruby
P <quantity> <unit> [ <quantity> <unit> ...] [ T [ <quantity> <unit> ...]]
```

- ISO 8601 format always starts with `P`.
- `quantity` and `unit` before `T` represents years, months, weeks and days of
  an interval.
- `quantity` and `unit` after `T` represents the time-of-day unit.

```ruby
# Examples
P1Y1M1D => interval of '1 year 1 month 1 day'
P3Y1DT2H => interval of '3 years 1 day 2 hours'
P5Y2MT3H2M => interval of '5 years 2 months 3 hours 2 minutes'
# NOTE: If `M` appears before `T`,
# it is month/months and if it appears after `T`, it signifies minute/minutes.

OR

P [ years-months-days ] [ T hours:minutes:seconds ]

# Examples
P0012-07-00T00:09:00 => interval of '12 years 7 months 9 minutes'
P0000-10-00T10:00:00 => interval of '10 months 10 hours'
```

#### Arithmetic operations on interval

We can easily apply addition, subtraction and multiplication operations on
interval data.

```ruby
'10 hours 10 minutes' + '30 minutes' => '10 hours 40 minutes'

'10 hours 10 minutes' - '10 minutes' => '10 hours'

60 * '10 minute' => '10 hours'
```

### Before Rails 6.1

PostgreSQL `interval` data type can be used in Rails but Active Record treats
`interval` as a string. In order to convert it to an `ActiveSupport::Duration`
object, we have to manually alter the `IntervalStyle` of the database to
`iso_8601` and then parse it as shown below:

```ruby
execute "ALTER DATABASE <our_database_name> SET IntervalStyle = 'iso_8601'"

ActiveSupport::Duration.parse(the_iso_8601_formatted_string)
```

### Rails 6.1

Rails 6.1 adds built-in support for the PostgreSQL `interval` data type. It
automatically converts `interval` to an `ActiveSupport::Duration` object when
fetched from a database. When a record containing the `interval` field is saved,
it is serialized to an ISO 8601 formatted duration string.

The following example illustrates how it can be used now:

```ruby
# db/migrate/20201109111850_create_seminars.rb

class CreateSeminars < ActiveRecord::Migration[6.1]
  def change
    create_table :seminars do |t|
      t.string :name
      t.interval :duration
      t.timestamps
    end
  end
end

# app/models/seminar.rb
class Seminar < ApplicationRecord
  attribute :duration, :interval
end

>> seminar = Seminar.create!(name: 'RubyConf', duration: 5.days)
>> seminar
=> #<Event id: 1, name: "RubyConf", duration: 5 days, created_at: ...>

>> seminar.duration
=> 5 days

>> seminar.duration.class
=> ActiveSupport::Duration

>> seminar.duration.iso8601
=> "P5D"

# ISO 8601 strings can also be provided as interval's value
>> seminar = Seminar.create!(name: 'GopherConIndia', duration: 'P5DT7H6S')
>> seminar
=> #<Event id: 2, name: "GopherConIndia", duration: 5 days, 7 hours, and 6 seconds, created_at: ...>

# Invalid values to interval are written as NULL in the database.
>> seminar = Seminar.create!(name: 'JSConf', duration: '3 days')
>> seminar
=> #<Event id: 3, name: "JSConf", duration: nil, created_at: ...>

```

If we want to keep the old behaviour where `interval` is treated as a string, we
need to add the following in the model.

```ruby
# app/models/seminar.rb
class Seminar < ApplicationRecord
  attribute :duration, :string
end
```

If the `attribute` is not set in the model, it will throw the following
deprecation warning.

```plaintext
DEPRECATION WARNING: The behavior of the `:interval` type will be changing in Rails 6.2
to return an `ActiveSupport::Duration` object. If you'd like to keep
the old behavior, you can add this line to Event model:

  attribute :duration, :string

If you'd like the new behavior today, you can add this line:

  attribute :duration, :interval
```

Check out the
[commit](https://github.com/rails/rails/commit/0475215d4fa1a6db2a92a0065081fe19c64cc124)
for more details.

## Links

- [Human page](https://www.bigbinary.com/blog/rails-6-1-adds-postgresql-interval-data-type)