What timezones are the timestamps set in?


#1

Snowplow captures a whole range of timestamps. We sometimes get the question: what timezones are those timestamps set in? Here’s the answer:

  • collector_tstamp: UTC
  • dvce_created_tstamp: UTC
  • dvce_sent_tstamp: UTC
  • etl_tstamp: UTC
  • true_tstamp: user defined
  • derived_tstamp: UTC or user defined (depending on whether true_tstamp is set)

You can use CONVERT_TIMEZONE to convert between timezones once the data is in Amazon Redshift.


#2

Adding to that, it’s often helpful to combine the timestamps with the inferred time zones from geo_timezone and os_timezone like so:

convert_timezone('UTC', os_timezone, derived_tstamp).

Makes life easier to surface trends in day parting by with the user’s local time.