Best way to get all entries for one day, or date range, using Ecto.Query?

Hi! I spent a long time trying to figure out the best way to get all entries in a specific date range. So wanted to share my solution and see if there’s a better way.

Is this the best way to go about it? Set all NaiveDateTime’s time to 00:00:00.0000 for today and tomorrow, then get today >= and < tomorrow’s entries.

  @seconds_in_a_day 24 * 60 * 60
  def get_list_of_today_values(utc_date) do
    {:ok, time} = Time.new(0, 0, 0, 0)

    {:ok, today_date} = \
      utc_date \
      |> NaiveDateTime.to_date() \
      |> NaiveDateTime.new(time)

    next_day_date = today_date |> NaiveDateTime.add(@seconds_in_a_day, :second)

    from(ss in StorageStat, where: ss.measured_at >= ^today_date and ss.measured_at < ^next_day_date)
    |> Repo.all()
  end

I use the following code to search for “published” records in a database - that means records that are available only during a specific period of time (date range):

from(q in queryable 
  where: fragment("now() between ? and coalesce(?, now())", q.start_date, q.end_date)
)

Since the range can be open-ended (end_date == NULL), I use coalesce to avoid problems. Other than that, Postgres between and now() functions are very useful.

If you want to return all entries from said date then how about simply converting PostgreSQL’s timestamp to date?

Here is an example script:

Mix.install([:ecto_sql, :postgrex])

defmodule Migration do
  use Ecto.Migration

  def change do
    create table("tests") do
      add(:timestamp, :naive_datetime)
    end
  end
end

defmodule Repo do
  use Ecto.Repo, adapter: Ecto.Adapters.Postgres, otp_app: :my_app
end

defmodule Test do
  use Ecto.Schema

  schema "tests" do
    field(:timestamp, :naive_datetime_usec)
  end
end

defmodule Example do
  alias Ecto.Query
  require Query

  def cleanup do
    Repo.stop()
  end

  def prepare do
    Application.put_env(:my_app, Repo,
      database: "example",
      password: "postgres",
      username: "postgres"
    )

    _ = Repo.__adapter__().storage_down(Repo.config())
    :ok = Repo.__adapter__().storage_up(Repo.config())
    {:ok, _} = Supervisor.start_link([Repo], strategy: :one_for_one)
    Ecto.Migrator.up(Repo, 0, Migration)
  end

  def sample do
    now = NaiveDateTime.utc_now()

    Test
    |> Query.from(as: :test)
    |> Query.where(
      [test: test],
      fragment("?::date = ?::date", test.timestamp, type(^now, :naive_datetime))
    )
    |> Query.select([test: test], test.timestamp)
    |> Repo.all()
    |> IO.inspect()
  end

  def seed do
    seconds_in_day = 24 * 60 * 60
    now = NaiveDateTime.utc_now()
    yesterday = NaiveDateTime.add(now, seconds_in_day * -1, :second)
    tomorrow = NaiveDateTime.add(now, seconds_in_day, :second)
    Repo.insert(%Test{timestamp: yesterday})
    Repo.insert(%Test{timestamp: now})
    Repo.insert(%Test{timestamp: tomorrow})
  end
end

Example.prepare()
Example.seed()
Example.sample()
Example.cleanup()

If you’re using Timex, there are utility functions for this (beginning_of_day / end_of_day).

Something to think about with this: what happens at a summer-time boundary? Those “days” can be either 25 or 23 hours long…