Programming things such as recurring events or events with dates that depend on each other are not the easiest thing a developer can set out to do.
If you’ve ever worked with a calendar or scheduling application, you might have found yourself calculating a set of dates and saving them to your database, or scheduling a bunch of background jobs to run at given times. You might have also prayed for those dates you calculated and scheduled never to change because it’s hard to wrap your head around what will happen if you try to update them.
In this article, I suggest an alternative approach that delegates all the hard work to the database. It uses Ruby on Rails as the example web framework, but as long as you’re using PostgreSQL, this may prove useful regardless of which programming language or framework you’re using. It all relies on PostgreSQL’s interval data type.
What is an Interval?
In Postgres, interval is a data type used to represent durations or offsets from a base date or time. If you want to get all scientific about it, you can start in the datetime functions documentation.
The interface is ridiculously flexible and intuitive. It’s almost as if you could let your users type some amount of time into a text field and throw that straight into your database (which I vehemently discourage). The query below shows some of that flexibility:
SELECT
(now() + interval '1 hour, 5 minutes, 3 seconds') a,
(now() + interval '65 minutes, 3 seconds') b,
(now() + interval '3903 seconds') c;
Results in the row below. Note that a, b, and c all have the same values:
a | b | c
-------------------------------+-------------------------------+-------------------------------
2020-03-20 11:22:03.623447+01 | 2020-03-20 11:22:03.623447+01 | 2020-03-20 11:22:03.623447+01
The example above shows how flexible the syntax is. Still, if this is your first time interacting with intervals, you are probably questioning whether this is more than syntactic sugar on top of adding an integer to a date.
Now, let’s say we are developing a streaming service. We charge our users when they sign up for the service, and we bill them monthly. A month can have 28, 29, 30, or 31 days so just adding the same value over and over wouldn’t work. Interval makes that very simple, though. Note how adding one month and two months to the 31st of January will give us the 29th of February and the 31st of March, respectively:
SELECT(date '2020-01-31' + interval '1 month') D;
d
---------------------
2020-02-29 00:00:00
(1 row)
SELECT(date '2020-01-31' + interval '2 months') D;
d
---------------------
2020-03-31 00:00:00
Interval on Rails
With ActiveRecord, you can declare interval as the type of a column in your
database table. Here, frequency is an interval:
create_table :recurring_events do |t|
t.string :title
t.datetime :first_occurrence_at, null: false
t.interval :frequency, null: false
end
This will allow you to easily create records:
> recurring_event = RecurringEvent.create(first_occurrence_at: '2020-01-25', frequency: '1 month')
> recurring_event
=> #<RecurringEvent id: 1, title: nil, first_occurrence_at: "2020-01-25 00:00:00", frequency: "1 month">
> recurring_event.frequency
=> "1 month"
Now, with a database view, we can list our event occurrences. The following view will return the title and date for the first 1000 occurrences.
CREATE VIEW occurrences AS WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 1000
) SELECT
title,
first_occurrence_at + n * frequency AS happening_at
FROM t INNER JOIN recurring_events ON TRUE
With the database view to back it up, we can create our model:
# app/models/occurrence.rb
class Occurrence < ApplicationRecord
end
And if we want to list our first ten occurrences, it is as easy as:
pp Occurrence.order(:happening_at).limit(10).pluck(:happening_at)
(2.6ms) SELECT "occurrences"."happening_at" FROM "occurrences" ORDER BY "occurrences"."happening_at" ASC LIMIT $1 ["LIMIT", 10]]
[Tue, 25 Feb 2020 00:00:00 UTC +00:00,
Wed, 25 Mar 2020 00:00:00 UTC +00:00,
Sat, 25 Apr 2020 00:00:00 UTC +00:00,
Mon, 25 May 2020 00:00:00 UTC +00:00,
Thu, 25 Jun 2020 00:00:00 UTC +00:00,
Sat, 25 Jul 2020 00:00:00 UTC +00:00,
Tue, 25 Aug 2020 00:00:00 UTC +00:00,
Fri, 25 Sep 2020 00:00:00 UTC +00:00,
Sun, 25 Oct 2020 00:00:00 UTC +00:00,
Wed, 25 Nov 2020 00:00:00 UTC +00:00]
Updating those values is as simple as updating our RecurringEvent record.
Change first_occurrence_at for the base date and change frequency for the
duration.
recurring_event.update(frequency: '2 weeks', first_occurrence_at: '2020-03-20')
pp Occurrence.order(:happening_at).limit(10).pluck(:happening_at)
(2.3ms) SELECT "occurrences"."happening_at" FROM "occurrences" WHERE ORDER BY "occurrences"."happening_at" ASC LIMIT $1 [["LIMIT", 10]]
[Fri, 03 Apr 2020 00:00:00 UTC +00:00,
Fri, 17 Apr 2020 00:00:00 UTC +00:00,
Fri, 01 May 2020 00:00:00 UTC +00:00,
Fri, 15 May 2020 00:00:00 UTC +00:00,
Fri, 29 May 2020 00:00:00 UTC +00:00,
Fri, 12 Jun 2020 00:00:00 UTC +00:00,
Fri, 26 Jun 2020 00:00:00 UTC +00:00,
Fri, 10 Jul 2020 00:00:00 UTC +00:00,
Fri, 24 Jul 2020 00:00:00 UTC +00:00,
Fri, 07 Aug 2020 00:00:00 UTC +00:00]
Creating Records
The very flexible interface for writing into interval fields allows us to
quickly build intuitive forms that are easy to translate into a format accepted
by the database.
<%= form_for RecurringEvent.new do |f| %>
<%= f.number_field :years %> year(s)
<%= f.number_field :months %> month(s)
<%= f.number_field :days %> day(s)
<%= f.number_field :hours %> hour(s)
<%= f.submit %>
<% end %>
def create
frequency = ["#{event_params.fetch(:years, 0)} years",
"#{event_params.fetch(:months, 0)} months",
"#{event_params.fetch(:days, 0)} days",
"#{event_params.fetch(:hours, 0)} hours"].join(', ')
# frequency => 'x years, x months, x days, x hours'
RecurringEvent.create!(frequency: frequency)
end
And we could list upcoming occurrences very easily too:
Occurrence.where('happening_at > ?', DateTime.current)
Limitations
At the time of writing this article, ActiveRecord casts intervals to strings.
> RecurringEvent.first.frequency.class
=> String
Unfortunately, you can’t just add a DateTime and a string, which makes the task of manipulating these values in Ruby not trivial.
Maybe you and I could buddy up and write a monkey patch to ActiveRecord so it can handle intervals more nicely? If you have any comments or suggestions when dealing with dates, I would love to hear from you.
You can write to me at jonas@thoughtbot.com
