Using existing entries in an Ecto changeset function

I have a business_hours table (using MySQL) which contains the following fields:

location_id (foreign key)
starting_time (float [e.g. 17 = 5pm, 17.5 = 5:30pm])
ending_time (float)
day (integer [0 = monday, 6 = sunday])

My question is how would I go about creating an Ecto changeset function to validate that a new business_hours entry does not overlap any existing entry? I know how I would check for this in general (see below), but I am unsure on how to integrate this into a changeset function to validate this serverside.

(a.starting_time > b.ending_time or a.ending_time < b.starting_time) and a.day == b.day and a.location_id == b.location_id

Remember that a changeset is a struct like any other, and the built-in validation functions accept an %Ecto.Changeset{} struct as input. You can write your own functions that also accept this as input and you can add errors or values by modifying the changeset manually or by using the provided helper functions (e.g. Ecto.Changeset.add_error/2).

For example, in your Ecto schema module, the convention is to do validation within a changeset/2 function (but you could put this logic anywhere):

  def changeset(existing_data, attrs) do
    existing_data
    # force_changes to true is a helpful option if you need to have all
    # values preset for evaluation even if they are unchanged from what's
    # already in the database
    |> cast(attrs, [:location_id, :starting_time, :ending_time, :day], force_changes: true)
    |> validate_required([:location_id, :starting_time, :ending_time, :day])
    |> foreign_key_constraint(:location_id)
    |> custom_business_logic()
  end

  # This function may require force_changes: true
  defp custom_business_logic(%Ecto.Changeset{changes: %{starting_time: starting_time, ending_time: ending_time, day: day}} = changeset) do
    case check_for_overlap(starting_time, ending_time, day) do
      :ok -> changeset
      _ -> add_error(changeset, :starting_time, "Uh oh... this time overlapped with some other location")
    end
  end

  defp check_for_overlap(starting_time, ending_time, day) do
    # do custom logic check here, e.g. query database
    # return :ok or {:error, "Something"}
  end

From your description, you may need to have your custom function query the database, so that query could be housed in one of the custom validation functions.