Best approach to query data on specific dates?

Hey there.

I would like to know what is the best approach to get data for specific dates, for instance, I want to create a chart displaying the users registered from Monday through Sunday, see screenshot below:

How would you guys structure your queries?

Thanks for your time.

Sounds like a normal aggregate, or you could get more fancy with a pivot query. Easier to show examples if you show all relevant schemas. :slight_smile:

My schema is very basic, I have not added any date fields apart from the default ones (inserted_at, updated_at)

schema "movies" do
    field :title, :string
    field :description, :string


You’re basically looking at a SELECT DATE(inserted_at), count(*) GROUP BY DATE(inserted_at).

*Using proper syntax for the equivalent of DATE for your database of course.


Exactly, and Ecto syntax supports it too as I recall. :slight_smile:

1 Like