BerandaComputers and TechnologyRecurring Events and PostgreSQL

Recurring Events and PostgreSQL

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
=> #
> 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

Read More

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments