BigBinary Blog
We write about Ruby on Rails, React.js, React Native, remote work, open source, engineering and design.
Rails 6.1 adds support for PostgreSQL interval data type
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:
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'quantitycan be any number.unitcan be any granular unit of time in plural or singular form like days/day, months/month, weeks/week, etc..directioncan beagoor an empty string.
2. ISO 8601 formats:
1P <quantity> <unit> [ <quantity> <unit> ...] [ T [ <quantity> <unit> ...]]- ISO 8601 format always starts with
P. quantityandunitbeforeTrepresents years, months, weeks and days of an interval.quantityandunitafterTrepresents 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
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
4endIf 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.
