title: Firebase BigQuery server offset time
body: <p><strong>Background:</strong>
  I'm having the Firebase analytics data exported to BigQuery. And I'm using cron jobs to crunch data in BigQuery for getting insight.</p>
  
  <p><strong>Problem:</strong>
  To be able to only crunch delta data i.e. the data that has arrived since last time I ran my cron job I need a way to figure out the time when the data arrived at server, since the <em>event_timestamp</em> is generated at client and can be cached at client before sent.</p>
  
  <p><strong>Insights:</strong>
  I have laborated with <em>event_server_timestamp_offset</em> (offset) which I thought I could use together with <em>event_timestamp</em>. But I was expecting the offset to only be positive but it can also be negative. And when I look at the MAX and MIN for the offset in the entire exported Firebase analytics dataset and re-calculate it to years instead of microseconds I can get more than 18 years offset.</p>
  
  <p><strong>Query:</strong></p>
  
  <pre><code>SELECT
    MAX(event_server_timestamp_offset)/(1000000*60*60*24) max_days, 
    MIN(event_server_timestamp_offset)/(1000000*60*60*24) min_days
  FROM
    `analytics_&lt;project_id&gt;.events_*`
  </code></pre>
  
  <p>Result: max_days=6784.485790436655,
  min_days=-106.95833052104166</p>
  
  <p><strong>Question:</strong></p>
  
  <ul>
  <li>How can I figure out the server arrival time for my Firebase exported BigQuery data so I can run cron jobs crunching only delta data?</li>
  <li>Can I use <em>event_server_timestamp_offset</em> together with <em>event_timestamp</em>? If so, how?</li>
  </ul>
  
  <p>Best regards,
  Daniel</p>