How to aggregate by dates?

Felix Tan-2030210

Hello!

Let's say I have a list of timestamps and I would like a count of number of timestamps by each date. How would I do this?

So for example, given a list of 20 timestamps, the ideal output would be:

• 9th Nov: 10
• 10th Nov: 3
• 11: Nov: 7

Chris Coleman

Hey Felix, just following up on this. Unfortunately, you would need a secondary pass here. I would do the query all records, and then figure out how many possible values can exist within the range. Create a new variable called newList and then loop through the total possible values.

You can use the Array function to find first element. If you can find a match on each iteration, use that - otherwise use 0.

Michael Udinski

I would consider two approaches:

Group By the timestamp 2 or 3 times (explained below) - my example is created_at.

Add a filter for each timestamp:

• timestamp_year (use if you need to separate data across years)
• timestamp_month
• timestamp_day_of_month

Or, you could use one Group By on the timestamp and use the timestamp_epoch_day.

Then just aggregate by id and use the count aggregator.

I personally like the top solution because it provides the group by in a more human-readable format

Felix Tan-2030210

Certainly prefer the top one too @Michael Udinski :)

While this works for most cases, I'm trying to display a graph of counts by day. So if there was a day where there was a count of 0, I'd like that in the response as well.

I'm guessing the above method wouldn't work then, because it's grouping by the "created_at", and there wouldn't be any records created on a day where the count is 0.

Would it be necessary to then do a loop through of all days of the month? Or is there a more efficient way that you know of?

Ray Deck

The database only holds the data it has and reports on it. What you want is common: a structure that includes all categories (e.g. the dates in question) that include where the database does not have any data for that "cell". You're looking to insert cells for data the database does not have. You can do that! After the database query, iterate over a list of your dates and check to see if your query result contains a matching record. If it does, add that summary stat (e.g. count, sum etc) and if not, add a zero to the resulting output.