Using Duration with Ecto+Postgrex

Duration: a useful time interval representation

Duration is covered by ISO 8601 standard that deal with time data and defines the amount of time in a given interval

One might think in the simpler and classic example to explain a duration: the difference between two dates or time

1. What is the age of a person?
2. How many days to the due date of that expensive loan?
3. The countdown in days or hours to the most anticipated concert

Thus a data type to represent a duration of a given time interval becomes useful in many use cases

The readable representation for human beings of a duration is, according to the ISO 8601 standard, a letter representing the unit of time followed by a number representing the amount of this unit. So here we go:

P is a marker used to split the Período portion of a given duration, represented as such:

T is a marker used to split the Time portion of a given duration, represented as such:

The P3W2DT10H45M represents a duration of 3 weeks, 2 days, 10 hours e 45 minutes

Ecto, Postgrex and Duration

The Elixir structure %Duration{} and its functions were introduced in the version 1.17 and in previous versions, according to the documentation, the used structure to decode time intervals on PostgreSQL was Postgrex.Interval that so far is still the default structure

In the following snippets on can see the schema and migration definitions for a fictitious set of data as well as the materialization on database

Schema

schema "records" do
  field :name, :string
  field :description, :string 
  field :start, :utc_datetime
  field :duration, :duration
end

Migration

def change do
   create table(:records, primary_key: false) do
     add :id, :uuid, primary_key: true
     add :name, :string
     add :description, :string
     add :start, :utc_datetime
     add :duration, :duration

     timestamps(type: :utc_datetime)
   end
 end

Table Structure

Column    |              Type              | Collation | Nullable | Default
-------------+--------------------------------+-----------+----------+---------+
 id          | uuid                           |           | not null |         |
 name        | character varying(255)         |           |          |         |
 description | character varying(255)         |           |          |         |
 start       | timestamp(0) without time zone |           |          |         |
 duration    | interval                       |           |          |         |

Persisted Data

id                  |    name    |     description      | start | duration |
--------------------------------------+------------+----------------------+-------+----------+
 664e5fd6-17ab-4a27-8799-9ebca4c603e5 | Record AA | The Record AA descr   |       | 11 days  |

What went wrong and why I brought this topic?

As the default structure mapped to PostgreSQL's intervals is Postgrex.Interval when the :duration type is defined in the schema and also in the migrations the value is correctly persisted in the other hand when it's retrieved the interval cannot be mapped to %Duration{} and the following error is thrown:

(ArgumentError) cannot load %Postgrex.Interval{months: 0, days: 11, secs: 0, microsecs: 0} as type :duration for field :duration

When someone is working in a product is desired to keep the focus in the work being done and mainly in the business domain, sometimes mixed up infrastructure code or external libraries might cause technical debts, maintenance and perhaps the lack of code readability and clearness

It's important to ensure a good level of homogenized code, leverage the usage of standard libraries. In the case of Duration structure it was supported but due my lack of knowledge it was not working properly.

I figure out that Postgrex.Interval was the default structure to decode PostgreSQL's intervals came through several researches about how to solve the error above

Learning is a constant in the software industry, however is not always possible to spend time as one would wish to acquire new knowledge, explore new libraries or frameworks, etc. Quite often developer seek for shortcuts that could solve the issue and allow them to move forward in a faster way

I've search exhaustively but I couldn't find nothing like a copy & paste to commit my code and end my day, so I start collecting pieces of information here and there stopping of being a lazy developer and reading more thedocumentation perhaps it may save some time from someone on future

I am wondering that I might not have found more info 'cause I didn't use the right keywords or by insisting to use DuckDuckGo as it sounds the results lately are poorer than before

Configuration

In a nutshell the configuration of Duration as interval decoding is done in two main files: config.exs e postgres_types.ex (the last one might have any name, postgres_types.ex is just a natural suggestion)

The file postgres_types.ex contains the configuration to overwrite the default one Postgrex.Interval, looking to file config.exs it associates the custom type to Ecto.Repo

Let's move forward and prepare the so expected snippet of code to copy, paste and close the day, thus the configuration that makes everything to work in a almost magical way (almost magical because in the end of day it's code design made by awesome guys behind the language, libraries and frameworks)is shown below:

# config/config.exs
# configures postgrex types

import Config

config :my_app,
  ecto_repos: [MyApp.Repo],
  generators: [timestamp_type: :utc_datetime]

# configures postgrex types
config :my_app, MyApp.Repo, types: MyApp.PostgresTypes

# Configures the endpoint
(config continues...)

# lib/my_app/postgres_types.ex
# set the interval decode

Postgrex.Types.define(
  ConstruaApi.PostgresTypes,
  [] ++ Ecto.Adapters.Postgres.extensions(),
  interval_decode_type: Duration
)
# --- that's all folks ---

It works

The search for a simple example, straight to the point doesn't work for me and in my rush to figure out the solution I have forgotten to ask to the available AI mechanisms on how to solve the issue in an easy and fast way (Doh!)

Now when someone seek for a handy code to solve this problem or a similar one here they have, but it's important to not forget (like me) the GPTs, Geminis and other AIs around to help

a pt_BR version is available here