This blog is part of our Rails 6.1 series.
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.
- Verbose format:
1 <quantity> <unit> [<quantity> <unit>...] [<direction>] 2 3 # Examples: 4 '2 years ago' 5 '12 hours 13 minutes ago' 6 '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.
- ISO 8601 formats:
1P <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.
1# Examples 2P1Y1M1D => interval of '1 year 1 month 1 day' 3P3Y1DT2H => interval of '3 years 1 day 2 hours' 4P5Y2MT3H2M => interval of '5 years 2 months 3 hours 2 minutes' 5# NOTE: If `M` appears before `T`, 6# it is month/months and if it appears after `T`, it signifies minute/minutes. 7 8OR 9 10P [ years-months-days ] [ T hours:minutes:seconds ] 11 12# Examples 13P0012-07-00T00:09:00 => interval of '12 years 7 months 9 minutes' 14P0000-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.
1'10 hours 10 minutes' + '30 minutes' => '10 hours 40 minutes' 2 3'10 hours 10 minutes' - '10 minutes' => '10 hours' 4 560 * '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:
1execute "ALTER DATABASE <our_database_name> SET IntervalStyle = 'iso_8601'" 2 3ActiveSupport::Duration.parse(the_iso_8601_formatted_string)
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:
1# db/migrate/20201109111850_create_seminars.rb 2 3class CreateSeminars < ActiveRecord::Migration[6.1] 4 def change 5 create_table :seminars do |t| 6 t.string :name 7 t.interval :duration 8 t.timestamps 9 end 10 end 11end 12 13# app/models/seminar.rb 14class Seminar < ApplicationRecord 15 attribute :duration, :interval 16end 17 18>> seminar = Seminar.create!(name: 'RubyConf', duration: 5.days) 19>> seminar 20=> #<Event id: 1, name: "RubyConf", duration: 5 days, created_at: ...> 21 22>> seminar.duration 23=> 5 days 24 25>> seminar.duration.class 26=> ActiveSupport::Duration 27 28>> seminar.duration.iso8601 29=> "P5D" 30 31# ISO 8601 strings can also be provided as interval's value 32>> seminar = Seminar.create!(name: 'GopherConIndia', duration: 'P5DT7H6S') 33>> seminar 34=> #<Event id: 2, name: "GopherConIndia", duration: 5 days, 7 hours, and 6 seconds, created_at: ...> 35 36# Invalid values to interval are written as NULL in the database. 37>> seminar = Seminar.create!(name: 'JSConf', duration: '3 days') 38>> seminar 39=> #<Event id: 3, name: "JSConf", duration: nil, created_at: ...> 40
If we want to keep the old behaviour where interval is treated as a string, we need to add the following in the model.
1# app/models/seminar.rb 2class Seminar < ApplicationRecord 3 attribute :duration, :string 4end
If the attribute is not set in the model, it will throw the following deprecation warning.
1DEPRECATION WARNING: The behavior of the `:interval` type will be changing in Rails 6.2 2to return an `ActiveSupport::Duration` object. If you'd like to keep 3the old behavior, you can add this line to Event model: 4 5 attribute :duration, :string 6 7If you'd like the new behavior today, you can add this line: 8 9 attribute :duration, :interval
Check out the commit for more details.