sheharyarn/memento

Select by date

Closed this issue · 2 comments

micheleriva commented

Hi! First of all, thanks for this library, is awesome!
I have a little problem querying Mnesia by date: I need to get every record where :creation_date is less than 24 hours ago.

I am writing the following method:

    def select_by_date do
     date = Timex.shift(NaiveDateTime.utc_now, hours: -24) 
     run_query(
        {:<, :created_at, date}
      )
    end

    defp run_query(pattern) do
      Memento.transaction! fn ->
        @store 
        |> Memento.Query.select(pattern)
      end
    end

For the run_querymethod, I've followed your example.

When I run the select_by_date method, I get the following error:

iex(1)> delete_outdated_config
** (Memento.MnesiaException) Mnesia operation failed
   Bad or invalid argument, possibly bad type
   Mnesia Error: {:badarg, [Engine.Mnesia.Db.Config, [{{Engine.Mnesia.Db.Config, :"$1", :"$2", :"$3", :"$4", :"$5", :"$6", :"$7"}, [{:<, :"$6", ~N[2019-01-23 16:50:45.690805]}], [:"$_"]}]]}
    (memento) lib/memento/query/query.ex:580: Memento.Query.select_raw/3
    (mnesia) mnesia_tm.erl:836: :mnesia_tm.apply_fun/3
    (mnesia) mnesia_tm.erl:812: :mnesia_tm.execute_transaction/5
    (memento) lib/memento/transaction.ex:71: Memento.Transaction.execute/2
    (memento) lib/memento/transaction.ex:84: Memento.Transaction.execute!/2

Is there a way to compare dates during the query?

sheharyarn commented

Hey @micheleriva.

While it's possible to use DateTime, NaiveDateTime and other special structs as values in Memento/Mnesia table records, they need to be normalized into a scalar type before you can perform queries on them (sort of similar to how you would do it with any other database).

Memento is meant to provide the absolute essentials and type conversions should be handled by you.
You could write helper functions that convert DateTime into unix timestamps when writing, and back to DateTime when reading. Once it's been normalized into an integer, you can use your query above to get the records.

For nested tuples or maps when dealing with complex data-types (such as datetime with timezone), you might want to consider using select_raw/3 or :qlc.

Here are some other links:

  • 👍1
micheleriva commented

Thank you so much, that's a clear answer!