Ecto: Ecto 3.1 %DateTime{} error with `date()` fragment

[BourgeoisBear]

Environment

• Elixir version (elixir -v): Elixir 1.9.0, Erlang/OTP 22
• Database and version (PostgreSQL 9.4, MongoDB 3.2, etc.): (PostgreSQL) 9.6.13
• Ecto version (mix deps): ~> 3.1
• Database adapter and version (mix deps): {:postgrex, "~> 0.14.3"},
• Operating system: Debian GNU/Linux 9.9 (stretch) (4.9.0-9-amd64)

Current behavior

This code to work with Ecto 2.2, but now throws a Postgrex expected %DateTime{}, got ~N[2018-11-07 11:26:56] error (like #2796) in Ecto 3.1 (even after @timestamps_opts [type: :naive_datetime_usec] adjustment made per Platformatec post):

   where([l],
      fragment("date(?) between date(?) and date(?)", l.inserted_at, ^start_date, ^end_date)
   )

However, this does work in Ecto 3.1:

   where([l],
     (l.inserted_at >= ^start_date) and (l.inserted_at <= ^end_date)
   )

Expected behavior

For date(?) fragments to behave as they did under Ecto 2.2.

[josevalim]

Because you are bypassing Ecto and using fragments, the database is the one dictating which input is necessary. That's why @timestamp_opts has no effect.

Also, can you please include the full stacktrace and error message? Thanks.

[BourgeoisBear]

Will have to revert to a prior version to reproduce the stack trace, so give me a day or two.

Also upgraded postgrex from 0.13.5 to 0.14.3. Was there some change in that driver which would alter how NaiveDateTime parameters are bound to the query? With the old libs, the fragment query ran successfully.

BTW, postgres columns are type timestamp without time zone for the date(?) fragments.

[josevalim]

We didn’t have naive and DateTime before, which is why the issue didn’t

appear.

José Valimwww.plataformatec.com.br
http://www.plataformatec.com.br/Founder and Director of R&D

[BourgeoisBear]

Could you clarify "didn't have"? From this post, it has handled NaiveDateTime since 2.1.

[josevalim]

Oh, sorry. I should have said they were not the defaults. It was most likely that you were using Ecto.DateTime before, no?

[BourgeoisBear]

Prior to 3.1 upgrade, @timestamp_opts was left as default. The value passed into the fragment was an Elixir NaiveDateTime.

[josevalim]

Ah, this does look like a regression then. Sometimes the Ecto versions are

all fuzzied in my head, I will take a look then, thanks!

José Valimwww.plataformatec.com.br
http://www.plataformatec.com.br/Founder and Director of R&D

[josevalim]

Looking at the code for Postgrex v0.14.3, we can see it handles both Naive and Datetime:

https://github.com/elixir-ecto/postgrex/blob/v0.14.3/lib/postgrex/extensions/timestamp.ex#L9-L21

But not for timestamptz:

https://github.com/elixir-ecto/postgrex/blob/v0.14.3/lib/postgrex/extensions/timestamptz.ex#L14-L21

So I believe you are getting the former and that indeed won't work with naive datetimes.

You can give a hint to PG by writing:

   where([l],
      fragment("date(?) between date(?::timestamp) and date(?::timestamp)", l.inserted_at, ^start_date, ^end_date)
   )

[josevalim]

In any case, it could indeed work on previous Ecto versions, but that's was when ecto implemented those callbacks and we have more lax rules on casting than Postgrex. :)