title: Firestore - How to aggregate data by time (in this example by hour)?
body: <p>I want to produce a real time graph like below (sort of like google analytics) on unique visitor graph. 
  After some investigation, I notice Firestore does not support GROUP BY like in SQL.</p>
  
  <p>Below in my sample data, no.1 and no.3 is within the same hour but of the same user_id = 1. So it should be counted as 1 and not 2. So, the number of person visited site "pen.com" at 10:00 hour is 1.</p>
  
  <p>How can I aggregate this data for site count unique visits time series, taking this example I am doing by hour grouping user_id ? Is there way to do this, since I see google analytics can do this, even in seconds (<a href="https://i.imgur.com/JfkcZqr.png" rel="nofollow noreferrer">https://i.imgur.com/JfkcZqr.png</a>)!</p>
  
  <p>Below are the example data and I want to aggregate the data to show the graph like below. The graph I drew is based on this sample data.</p>
  
  <p>tracking &lt;-- collection</p>
  
  <pre><code>  (10:00 hour)    
  
  1. document(aaa123) &lt;----------- inside 10:00 hour same place "pen.com"
  user_id = 1
  site_unique_identifier = "pen.com"
  timestamp = 10/09/2019 10:11
  
  2. document(xyz567)
  user_id = 2
  site_unique_identifier = "hex.com"
  timestamp = 10/09/2019 10:15
  
  3. document(qqq123) &lt;----------- inside 10:00 hour same place "pen.com"
  user_id = 1
  site_unique_identifier = "pen.com"
  timestamp = 10/09/2019 10:18
  
  4. document(xyz567)
  user_id = 3
  site_unique_identifier = "hex.com"
  timestamp = 10/09/2019 10:19
  
  5. document(fff567)
  user_id = 4
  site_unique_identifier = "hex.com"
  timestamp = 10/09/2019 10:19
  
    (11:00 hour)    
  
  6. document(xxx123)
  user_id = 1
  site_unique_identifier = "hex.com"
  timestamp = 10/09/2019 11:21
  
  7. document(zzz123)
  user_id = 2
  site_unique_identifier = "hex.com"
  timestamp = 10/09/2019 11:22
  
  8. document(ggg123)
  user_id = 2
  site_unique_identifier = "pen.com"
  timestamp = 10/09/2019 11:24
  </code></pre>
  
  <p>Based on the above sample data, below is a time series graph by the hour of unique visitors to various sites. (observe the 10:00 hour visit count is 1 instead of 2 to site "pen.com")</p>
  
  <p><a href="https://i.imgur.com/byQjD8w.jpg" rel="nofollow noreferrer"><img src="https://i.imgur.com/byQjD8w.jpg" alt="site tracking graph"></a></p>
  
  <p>output of the query to generate info for the graph (based on the same data):</p>
  
  <pre><code>10:00
  - pen.com - 1
  - hex.com - 3
  11:00
  - pen.com - 1
  - hex.com - 2
  </code></pre>
  
  <p>you could see we are grouping by user_id, site_unique_identifier, hour </p>