API & DataSync Release Notes for 8/14/2017: Time-Based SoQL Queries

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 support@socrata.com with questions or ideas.

Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request



Article is closed for comments.