🤩 Ecto's datetime helpers

Need expert help with Elixir and Phoenix? Let's work together!

Let's work together!

Notes

Ecto has some awesome helpers for dealing with date and datetime transformations in queries.

Unfortunately, I don’t think many people use them. In fact, I think most people don’t know about them!

So, let’s fix that.

Let’s write some queries

Suppose you want get all posts published before yesterday.

You might write a query like this:

today = Date.utc_today()
yesterday = Date.shift(today, day: -1)

from p in Post,
    where: p.published_date <= yesterday

Or you might just drop down to SQL and write a fragment:

from p in Post,
    where: p.published_date <= fragment("NOW() - INTERVAL '1 day'")

And to be fair, those work. I’ve used them in the past, and I’ve seen them in plenty of codebases.

But let me show you these sweet helpers!

`ago/2` and `from_now/2`

We can rewrite the queries above with ago/3:

from p in Post,
    where: p.published_date <= ago(1, "day")

Doesn’t that read that sooo nice? 😍

And of course, we have the counterpart from_now/2 if we want to get data from the future:

from p in Post,
    where: p.published_date <= from_now(1, "day")

(it may not make much sense with our “published” example, but just pretend you have a DeLorean)

Dropping down a level

But what if you don’t want to compare dates relative to now? What if we want to compare relative to another day?

Ecto’s got us covered! 😎

We can use date_add/3 and datetime_add/3 to drop down a level:

from p in Post,
    where: p.published_date <= date_add(^date, -1, "day")

from p in Post,
    where: p.published_date <= datetime_add(^datetime, -1, "day")

Drop any of those queries into a Repo function and you’re good to go!

🤩 Ecto's datetime helpers

Notes

Let’s write some queries

ago/2 and from_now/2

Dropping down a level

Want the latest Elixir Streams in your inbox?

`ago/2` and `from_now/2`