---
title: Recurring Events and PostgreSQL
teaser: 'Scheduling, recurring events and relative dates have always been hard. Until
  now.

  '
tags: postgresql,dates,interval,rails,ruby,duration,web,databases
author: Jonas Meinerz
published_on: 2020-03-24
---

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](https://www.postgresql.org/docs/12/functions-datetime.html).

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:

```sql
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`:

```ruby
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:

```ruby
> 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](https://github.com/scenic-views/scenic), we can
list our event occurrences. The following view will return the title and date
for the first 1000 occurrences.

```sql
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:

```ruby
# app/models/occurrence.rb

class Occurrence < ApplicationRecord
end
```

And if we want to list our first ten occurrences, it is as easy as:

```ruby
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.

```ruby
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 %>
```

```ruby
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:

```ruby
Occurrence.where('happening_at > ?', DateTime.current)
```

## Limitations

At the time of writing this article, ActiveRecord casts intervals to strings.

```ruby
> 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

## Useful Links

* [PostgreSQL datetime functions](https://www.postgresql.org/docs/12/functions-datetime.html)
* [Scenic gem - Database views for ActiveRecord](https://github.com/scenic-views/scenic)