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

Questions / Help
, , ,
1

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
2

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.

3

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()
1 Like
4

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…