Category: Date and time
EXTRACT
EXTRACT reads exactly one time unit from a DATE or TIMESTAMP, for example the hour or the day of week.
Syntax
EXTRACT(timeUnit FROM expression)AMC specifics
- Units allowed according to Amazon: SECOND, MINUTE, HOUR, DAY, DOW, WEEK, MONTH, YEAR.
- For time-of-day analysis convert the timestamp to the advertiser time zone first with CONVERT_TIME_ZONE_FROM_UTC, otherwise UTC applies.
Example
Impressions by hour of day (UTC).
SELECT
EXTRACT(HOUR FROM impression_dt_utc) AS hour_utc,
SUM(impressions) AS impressions
FROM
dsp_impressions
GROUP BY
1Checked against the AMC schema, not executed on an AMC instance.