Querying

[rockwood]

I'm having trouble querying by columns that use Timex.Ecto.DateTime. Is there something I'm doing wrong?

> {:ok, start_date} = DateFormat.parse("2015-10-01", "{YYYY}-{0M}-{0D}")
> Repo.all(from p in Post, where: v.submitted_at > ^start_date)


** (UndefinedFunctionError) undefined function: Timex.DateTime.type/0
    (ecto) lib/ecto/query/planner.ex:124: Ecto.Query.Planner.prepare/3
    (ecto) lib/ecto/query/planner.ex:66: Ecto.Query.Planner.query/4
    (ecto) lib/ecto/repo/queryable.ex:91: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:15: Ecto.Repo.Queryable.all/4

[bitwalker]

@rockwood Taking a look!

[bitwalker]

@rockwood Could you show me the Post model definition? Also just to clarify, which versions of ecto/timex_ecto are you using?

[bitwalker]

@rockwood It seems it's possible that the query builder is unable to infer the correct type of start_date, could you try replacing ^start_date with type(^start_date, Timex.Ecto.DateTime) to see if that works?

[rockwood]

"ecto": {:hex, :ecto, "1.0.1"},
"timex": {:hex, :timex, "0.19.4"},
"timex_ecto": {:hex, :timex_ecto, "0.5.0"},

defmodule App.Post do
  use App.Web, :model

  schema "posts" do
    field :submitted_at, Timex.Ecto.DateTime
  end
end

I tried switching to where: p.submitted_at > type(^start_date, Timex.Ecto.DateTime), but I get the same error: undefined function: Timex.DateTime.type/0

Thanks so much for taking a look.

[bitwalker]

@rockwood There is a bug in Ecto's query planner that I found. José/Eric should be taking a look before long. I'll keep this thread updated as I hear more!

[mspanc]

I am encountering the same issue, any news?

[bitwalker]

@mspanc Not yet, I had some ideas on the source of the issue, but haven't been able to delve into it to put together a fix + PR due to ElixirConf. I'll ping them again and see if they have any updates, but I'm guessing that it's been out of sight/out of mind for them since the issue was first reported.

[dyerc]

I too am facing this same issue. I tried converting to an Erlang DateTime, but if the field type is Timex.Ecto.Date it will try to cast it back again still yielding no success.

[paulcsmith]

This is a little ghetto, but is working for me until the bug is fixed.

# By formatting to ISO the conversion to the Timex field works correctly.
january_1 = Date.from({2015, 1, 1}) |> DateFormat.format!("{ISO}")
from(s in Subscription, where: s.created_at >= ^january_1)

P.S. I hope Timex becomes part of Elixir core at some point :) I think that would make things like this a lot simpler since there would be one common way of working with time

[bitwalker]

Sorry this is such a pain in the ass everyone, the issue goes beyond just timex unfortunately, and needs to be addressed within ecto, but there hasn't been time for the ecto team to address it, and while I put together a simple patch which fixes it in ecto, it's probably not the right solution for the long term. I'll keep pushing to get a solution merged ASAP!

[dyerc]

Thanks @paulcsmith that's very helpful. The field in my model is an Timex.Ecto.Date so using DateFormat.format!("{YYYY}-{0M}-{0D}") sorted this issue for me.

[paulcsmith]

@bitwalker It's all good! This is complicated stuff and we know you and the Ecto team have tons to do :) My hope is that if Timex were merged into Elixir core, there would not be as many/any issues with Timex/Ecto interop. Who knows. Maybe one day!

@CD1212 Glad I could help!

[bitwalker]

This should be fixed as of timex_ecto 0.7.0 / ecto 1.1. Could you all test and verify that this is working for you?

[paulcsmith]

@bitwalker I've been a bit busy this week, but I'll check this out and post here if there are any issues! :D

[oguizol]

Using timex_ecto 0.7.0 / ecto 1.1 I get the following debug output:
SELECT s0."id", s0."inserted_at", s0."updated_at", s0."name", s0."start_date", s0."end_date" FROM "my_table" AS s0 WHERE ((s0."start_date" >= $1) AND (s0."end_date" <= $2)) [{{2016, 2, 3}, {0, 0, 0, 0}}, {{2016, 2, 6}, {0, 0, 0, 0}}] OK query=215.8ms queue=13.4ms

I don't have the UndefinedFunctionError but I believe the parameters are not converted correctly.
I have the same output whether I put Timex dates or string formatted dates!

Is it working for anyone?
Thanks

[oguizol]

Nevermind. Looks like ecto dates output is the same.

[speeddragon]

I'm using DateTimeWithTimezone, and this error happen:

pry(13)> Table |> where([ur], ur.expire_date < ^Timezone.convert(Timex.now, "Europe/Copenhagen")) |> Repo.all
          ** (FunctionClauseError) no function clause matching in MyApp.PostgresTypes.encode_tuple/5
          iex(1)> [debug] QUERY ERROR source="table" db=1.5ms
          SELECT u0."id",  u0."reset_code", u0."expire_date", u0."inserted_at", u0."updated_at" FROM "table" AS u0 WHERE (u0."expire_date" < $1) [{{{2017, 8, 9}, {18, 6, 10, 218837}}, "Europe/Copenhagen"}]
                    (my_app) lib/postgrex/type_module.ex:717: MyApp.PostgresTypes.encode_tuple({{{2017, 8, 9}, {18, 6, 10, 218837}}, "Europe/Copenhagen"}, 1, nil, {MyApp.PostgresTypes, 1638460}, [])

I've tried DateTimeWithTimezone, Timezone, Datetime and can't get a query done successfully. It's possible to query using DateTimeWithTimezone or I need to convert for a simple date time format ?

[bitwalker]

How is your schema defined?