To aggregate datetimes, use ":hour", ":date", ":week", etc.
select [created_at:date] from users; select [created_at:week] from users; select [created_at:month] from users;
For example, let's take a look at new users by week:
Periscope supports the following date formatting options:
:sec :min :hour :day :date :month :year
To specify the aggregation across the whole dashboard using a filter, check out the Aggregation Filter.
Date formatters require a datetime or timestamp column type. Use ":ts" to convert an integer to a timestamp:
select [integer_created_at:ts:date] from users
Learn more about composing formatters this way with Composing SQL Formatters.