Strange error comparing NaiveDatetime in ecto

Questions / Help
1

Im working with dates on ecto, specifically the naive_datetime struct, dealing with some errors i found this strange behavior,

~N[2018-08-09 17:34:49] <  ~N[2018-08-09 17:34:50]
=> true

~N[2018-08-09 17:34:49.000000] <  ~N[2018-08-09 17:34:50]
=> false

My “improvised” solution was something like this

now =
    NaiveDateTime.utc_now()
    |> NaiveDateTime.truncate(:second)

Is it really an error? or, is it supposed to work that way?

p.s: I know there is a function NaiveDateTime.compare/2, but i cannot use that function into an ecto.query

Thanks :smiley:

1 Like
2

First of all, as you stated, you should use NaiveDateTime.compare/2 if you want to compare dates (as explained in the docs). In Ecto however, when you query on dates, the compare function isn’t used, but rather, the date is converted to a SQL-compatible date format and sent to the database (the Elixir comparison isn’t used). Do you also experience the same issue when querying to your database?

1 Like
3

Yes, I’m experiencing the same issue, but I think I got what you are saying, the problem is that all the examples I found about the topic compared the dates as I stated before, do you have an example?

4

This is a basic example of what I’m trying to do:

  def get_future_events() do
    now =
      NaiveDateTime.utc_now()
      |> NaiveDateTime.truncate(:second)
    from(
      e in Event,
      where: e.init_date >= ^now
    )
    |> Repo.all()
  end

field :init_date, :naive_datetime
5

Just found this topic which could be related? If you want to have microsecond precision work, I think you have to use :naive_datetime_usec but I’m not sure.

2 Likes
6

:wave:

Is the database running on the same machine as your elixir app? If not, then it’s safer to use the database’s now() function or something similar.

  def get_future_events() do
   from(
      e in Event,
      where: e.init_date >= fragment("now()") # something like that
    )
    |> Repo.all()
  end

As for the strange comparisons, they are not really strange – they are just key/element-wise map comparisons:

iex(1)> ~N[2018-08-09 17:34:49.000000] |> Map.from_struct
%{
  calendar: Calendar.ISO,
  day: 9,
  hour: 17,
  microsecond: {0, 6},
  minute: 34,
  month: 8,
  second: 49,
  year: 2018
}
iex(2)> ~N[2018-08-09 17:34:50] |> Map.from_struct
%{
  calendar: Calendar.ISO,
  day: 9,
  hour: 17,
  microsecond: {0, 0},
  minute: 34,
  month: 8,
  second: 50,
  year: 2018
}

which come down to {0, 6} > {0, 0} (first different value in the sorted keys list), hence ~N[2018-08-09 17:34:49.000000] > ~N[2018-08-09 17:34:50]. Note that you shouldn’t rely on these results since they are due to how the maps are currently implemented and the implementation can change.

3 Likes
7

Thank You soooo much!! :tada:, I appreciate the explanation now everything is clear.

:slight_smile:

8

But on the first struct the second is 49, and on the second 50 (second should be over microseconds on the comparison). I know I must not compare the structs directly, but is almost the only option when you are working with queries.

9

You can, as map keys are compared in term-order:

Maps are ordered by size, two maps with the same size are compared by keys in ascending term order and then by values in key order. In maps key order integers types are considered less than floats types.

http://erlang.org/doc/reference_manual/expressions.html#term-comparisons

:microseconds comes before :seconds in term order, so microseconds is compared first.

3 Likes
10

You don’t compare the structs in queries ever, actually. You compare their SQL representations, and the comparison itself is done in the database.

2 Likes