Best approach to query data on specific dates?

Phoenix Forum Questions / Help
1

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:

http://i.imgur.com/MJsgTVY.png?1
http://i.imgur.com/MJsgTVY.png?1664×645 5.67 KB

How would you guys structure your queries?

Thanks for your time.

2

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:

3

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

    timestamps()
end
4

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.

2 Likes
5

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

1 Like