title: Group Timestamps in minute intervals in AMC SQL - Amazon Marketing Cloud
body: <p>I am trying to grab event timestamps <code>event_dt</code> from an Amazon Marketing Cloud table <code>sponsored_ads_traffic</code>
  and in the output group <code>impressions</code> in continuous <code>n</code> minute interval buckets using an SQL statement.</p>
  <p>Reasoning for grouping:
  The AMC uses Data Privacy Filters which will remove all columns in the output if certain criteria on the amount of users per row are not met (e.g. 100 unique <code>user_id</code> per row), which makes it impossible to use full timestamps in the output. This filter is reflected in two columns that are appended by AMC automatically (  <code>privacy_user_count</code>, <code>privacy_is_filtered</code>.)</p>
  <p>I need to find the smallest possible timeframe that will return the most amount of data by looking at the results from 1,5,10,15 etc. minute intervals. I understand there is probably also a way to look at the timestamps and user_ids and see which interval is needed to satisfy &gt;100 user_ids but my SQL level is not good enough for that yet and so I decided to compare set intervals.</p>
  <p>Here is a sample of the desired output, using 5 minute intervals.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>timestamp_bucket</th>
  <th>sum_impressions</th>
  <th>privacy_user_count</th>
  <th>privacy_is_filtered</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2022-04-03T06:30:00.000Z</td>
  <td>143</td>
  <td>130</td>
  <td>false</td>
  </tr>
  <tr>
  <td>2022-04-03T06:35:00.000Z</td>
  <td>167</td>
  <td>146</td>
  <td>false</td>
  </tr>
  <tr>
  <td>2022-04-03T06:40:00.000Z</td>
  <td>156</td>
  <td>132</td>
  <td>false</td>
  </tr>
  <tr>
  <td></td>
  <td></td>
  <td>93</td>
  <td>true</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I can then compare the sum of total impressions over the same timespan from a separate query with the sum of impression not filtered in this query to gauge how much data is lost in each grouping interval and find the smallest interval with the most amount of data.</p>
  <p>The AMC product is currently in BETA and so far only available to select agencies and partners. I am unfortunately unable to share the full documentation and thus will try to provide neccesary information as needed. AMC SQL is limited in expressions and similar to PostgreSQL in syntax.</p>
  <p>Event Timestamps follow the format: 2022-04-03T06:32:17.453Z</p>
  <p>I was looking at similar questions and answers and have compiled a few hopefully relevant expressions below.</p>
  <p><strong>Extract</strong>- Get time unit from DATE or TIMESTAMP value. Time unit can be one of SECOND, MINUTE,
  HOUR, DAY, DOW, WEEK, MONTH, YEAR.</p>
  <pre><code>EXTRACT(timeUnit FROM expression)
  </code></pre>
  <p><strong>Seconds Between</strong> - Return number of seconds between two inputs temporal date types.</p>
  <pre><code>SECONDS_BETWEEN(firstInputTime, secondInputTime)
  </code></pre>
  <p><strong>Date Trunc</strong> - Truncate time to specified level of granularity. Granularity must be single quoted and can be
  one of SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR.</p>
  <pre><code>DATE_TRUNC('granularity', inputTime)
  </code></pre>