Search code examples
postgresqlsortingdatetimeelixirecto

How do you sort a table with datetime by time with Ecto?

I am hitting a wall at the moment. I am working with Elixir and Ecto and I have a table with data and a column with Datetime.

The Datetime column is as follows:

2017-11-16 16:02:01
2017-11-23 09:00:07
2017-11-27 13:19:58
2017-12-05 07:48:42

What I want to do is sort this table based on time, instead of date. So the result(ASC) would be:

2017-12-05 07:48:42
2017-11-23 09:00:07
2017-11-27 13:19:58
2017-11-16 16:02:01

Do you guys have any ideas in ecto? A postgres query might also help!

Solution

  • So I think something like this should work:

    Post 
|> order_by([p], fragment("?::time", p.inserted_at)) 
|> Repo.all()

    Here you use a fragment to in order to leverage the PostgreSQL casting mechanism which extracts the time part from the datetime. I have not tested it but I guess it should work.