Timex and Ecto: Troubleshooting

David Magalhães
Aug 14, 2017 · 2 min read

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!

Coletiv

Thoughts, dreams and rants about technology and work life…

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store