It has been 5 months since I started learning Elixir and Phoenix, and has been quite a ride. Most of the experience has been great but sometimes I find some issues that are hard to tackle.

A while ago I needed to use a better notion of time in our project, and after a little bit of research I found Timex and Timex Ecto library for Elixir.

Timex helps with all the necessary handling of time operations like conversions to a time unit, calculations for the difference between two date times, usage of timezones, etc. You can have a look at all the functionalities in the HexDocs. Timex Ecto applies that functionality to Ecto to be used with a database.

After using this library for some time I needed to make a query to PostgreSQL to delete all the rows before a certain date time. A normal query would be like the following:

MyApp.Model |> where([m], m.expire_date < ^Timezone.convert(Timex.now, “Europe/Copenhagen”)) |> Repo.delete_all

But the following error happened,

[error] #PID<0.514.0> running MyApp.Web.Endpoint terminated Server: localhost:4000 (http) Request: POST /api/auth/reset ** (exit) an exception was raised: ** (FunctionClauseError) no function clause matching in MyApp.PostgresTypes.encode_tuple/5 (my_app) lib/postgrex/type_module.ex:717: MyApp.PostgresTypes.encode_tuple({{{2017, 8, 9}, {22, 44, 59, 175265}}, “Europe/Copenhagen”}, 1, nil, {MyApp.PostgresTypes, 1638460}, []) (my_app) deps/postgrex/lib/postgrex/type_module.ex:717: MyApp.PostgresTypes.Postgrex.Extensions.Record/3 (my_app) deps/postgrex/lib/postgrex/type_module.ex:717: MyApp.PostgresTypes.encode_params/3 (postgrex) lib/postgrex/query.ex:45: DBConnection.Query.Postgrex.Query.encode/3 (db_connection) lib/db_connection.ex:1071: DBConnection.describe_run/5 (db_connection) lib/db_connection.ex:1142: anonymous fn/4 in DBConnection.run_meter/5 (db_connection) lib/db_connection.ex:1199: DBConnection.run_begin/3 (db_connection) lib/db_connection.ex:584: DBConnection.prepare_execute/4 (ecto) lib/ecto/adapters/postgres/connection.ex:80: Ecto.Adapters.Postgres.Connection.prepare_execute/5 (ecto) lib/ecto/adapters/sql.ex:243: Ecto.Adapters.SQL.sql_call/6 (ecto) lib/ecto/adapters/sql.ex:431: Ecto.Adapters.SQL.execute_and_cache/7 (my_app) lib/my_app/auth/auth_controller.ex:162: MyApp.Web.AuthController.reset/2 (my_app) lib/my_app/auth/auth_controller.ex:1: MyApp.Web.AuthController.action/2 (my_app) lib/my_app/auth/auth_controller.ex:1: MyApp.Web.AuthController.phoenix_controller_pipeline/2 (my_app) lib/my_app/web/endpoint.ex:1: MyApp.Web.Endpoint.instrument/4 (phoenix) lib/phoenix/router.ex:277: Phoenix.Router.__call__/1 (my_app) lib/my_app/web/endpoint.ex:1: MyApp.Web.Endpoint.plug_builder_call/2 (my_app) lib/plug/debugger.ex:123: MyApp.Web.Endpoint.”call (overridable 3)”/2 (my_app) lib/my_app/web/endpoint.ex:1: MyApp.Web.Endpoint.call/2 (plug) lib/plug/adapters/cowboy/handler.ex:15: Plug.Adapters.Cowboy.Handler.upgrade/4

After a bit of debugging I found a solution for this problem, but it was not a clean one. Because I’m using DateTimeWithTimezone I needed to create a new custom type in the database indicated here.

CREATE TYPE datetimetz AS ( dt timestamptz, tz varchar );

This structure represent the normal timezone type available in PostgreSQL (timestamptz) and the location of the timezone in format string. To query this structure my solution was to use the fragment function and check for the dt parameter. For example:

MyApp.Table |> where([ur], fragment(“(expire_date).dt < ?”, ^Timex.now)) |> Repo.delete_all

After talking with the @gotbones (creator of Timex), he helped me with the solution:

So the solution to your issues is this: When using query fragments on custom types, you have to specify the type of the object (because Ecto can’t know what the type is), you do this like so: where: u.datetime_field > type(^Timezone.convert(….), Timex.Ecto.DateTimeWithTimezone)

The final solution looks like this:

MyApp.Model |> where([ur], ur.expire_date < type(^Timezone.convert(Timex.now, "Europe/Lisbon"), Timex.Ecto.DateTimeWithTimezone)) |> Repo.delete_all

Finally, an happy ending for this issue and a better understanding how Ecto and Timex works!