Timex and Ecto: Troubleshooting

Timex and Timex Ecto Troubleshooting - Coletiv Blog

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:

|> 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:

|> 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:

|> 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!

Thank you for reading!

Thank you so much for reading, it means a lot to us! Also don’t forget to follow Coletiv on Twitter and LinkedIn as we keep posting more and more interesting articles on multiple technologies.

In case you don’t know, Coletiv is a software development studio from Porto specialised in Elixir, iOS, and Android app development. But we do all kinds of stuff. We take care of UX/UI design, web development, and even security for you.

So, let’s craft something together?