Aggregating Timestamps by Month, Day, or Year
When analyzing time series, data, it’s often useful to aggregate data to discover trends. Take, for example, this dataset from the City of Chicago reporting every towed car in the city. What if we wanted to aggregate it by month to see how many cars are towed in Chicago by month?
Fortunately, with the power of SoQL, this is fairly straightforward. You can use the date truncation functions on the dataset’s tow_date column, which is of the timestamp datatype. We’ll want to use the date_trunc_ym function to truncate the full timestamp down to just the year and month when the vehicle was towed.
Using that in conjunction with the $select and $group parameters, just like we’d do in traditional SQL, we’d end up with:
$select=date_trunc_ym(tow_date) AS month, count(*) AS total$group=month
It’s important to alias the results of date_trunc_ym(tow_date) in order to make them available to the $group.
Try date_trunc_ymd and date_trunc_y to see what happens!