Category: Date and time
DATE_TRUNC
DATE_TRUNC cuts a timestamp down to a unit such as week or month, so events can be grouped into periods.
Syntax
DATE_TRUNC('granularity', inputTime)AMC specifics
- The unit goes in single quotes. Amazon lists SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER and YEAR.
- Coarser units help against empty NULL rows: for groups that are too small Amazon suggests rolling days up to weeks or hours up to 4 to 6 hour windows.
Example
Impressions per week instead of per day or second.
SELECT
DATE_TRUNC('WEEK', impression_dt_utc) AS week_start,
SUM(impressions) AS impressions
FROM
dsp_impressions
GROUP BY
1Checked against the AMC schema, not executed on an AMC instance.