Query expression error when it tries to convert Ecto.data

Questions / Help
1

Hello, I want to convert my Ecto.data time to unix when I select in data base .

please see my code:

convertor:

	def nav_time_to_unix(time_to) do
		time_to 
		|> NaiveDateTime.to_erl 
		|> :calendar.datetime_to_gregorian_seconds 
		|> Kernel.-(62167219200)
	end

db select:

defp user_fields(query, group_acl) do
		from [p, c] in query,
		where: p.group_acl in ^group_acl,
		where: c.group_acl in ^group_acl,
		select: %{
			id: p.id,
			title: p.title,
			create_time: ^nav_time_to_unix(p.inserted_at),
                        .....
	end

but I have an error like this:

== Compilation error in file lib/cms/post/post_query.ex ==
** (Ecto.Query.CompileError) `^(p.inserted_at(), nav_time_to_unix())` is not a valid query expression.

* If you intended to call a database function, please check the documentation
  for Ecto.Query to see the supported database expressions

* If you intended to call an Elixir function or introduce a value,
  you need to explicitly interpolate it with ^

    expanding macro: Ecto.Query.select/3
    lib/cms/post/post_query.ex:59: TrangellCmsService.Cms.Post.PostQuery.admin_fields/1
    expanding macro: Ecto.Query.from/2
    lib/cms/post/post_query.ex:59: TrangellCmsService.Cms.Post.PostQuery.admin_fields/1
could not compile dependency :trangell_cms_service, "mix compile" failed. You can recompile this dependency with "mix deps.compile trangell_cms_service", update it with "mix deps.update trangell_cms_service" or clean it with "mix deps.clean trangell_cms_service"
2

You cannot run arbitrary functions as part of a query. Either do that after querying the db or use Ecto.Query.fragment() to create a valid SQL expression doing what your function currently does in elixir code.

2 Likes
3

Do I change that to this create_time: fragment("nav_time_to_unix(?)", p.inserted_at), ? because after changing I have an error , when I want to get post in query.

 ** (Postgrex.Error) ERROR 42883 (undefined_function): function nav_time_to_unix(timestamp without time zone) does not exist
        (ecto) lib/ecto/adapters/sql.ex:431: Ecto.Adapters.SQL.execute_and_cache/7
        (ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
        (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4
4

You cannot use elixir functions in a query. It either needs to be SQL or you need to do the transformation after you queried the data.

2 Likes
5

OHh, I have a problem in the time that I am forced to use this. how can I convert string time like this:

“2018-08-12 10:48:17.424416” to ~N[2018-08-12 10:48:17.424416] ? if I fix this problem, I needn’t that anymore.

6

That’s what ecto.types are for: https://hexdocs.pm/ecto/2.2.10/Ecto.Type.html

1 Like
7

I know, I take it in Json web service, and I have no Ecto in my client which takes the string time

8

Ecto.Type are not only for changing how external data is cast. You can make an ecto type that is a NaiveDateTime at runtime, but stores/reads from a string in the db.

1 Like