New Time-Based API Queries
Have you ever asked questions like “What time of day does crime happen more often in my city?” or “What days of the week do the most service requests come in? What about the least service requests?” or “What weeks during the year does my city see the most permit requests come through?”
Previously, if you wanted to answer those questions by querying data through the Socrata API, you needed to perform some computations on the date (and time, if applicable) data you got back for each row. To help you more easily parse out aggregated information, we have added new date extract functions, such as the day of week number (0 to 6 inclusive), or the hour and minute as distinct integers, and when combined with $select, $where, or $group, allow you to easily answer questions like the ones listed above.
Example of "day of week (dow)":
https://data.cityofchicago.org/resource/6zsd-86xi.json?$select=date_extract_dow(date) as day_of_week, primary_type, count(*)&$group=day_of_week, primary_type
These new functions are available with the SoQL 2.1 endpoints, which can be accessed through the API endpoint and documentation linked from every dataset on Primer.
Read the dev.socrata.com SoQL documentation for more details on how the new time-based querying works.
Check out our other Product News, and give us a shout at firstname.lastname@example.org with questions or ideas.