What timezones are the timestamps set in?

For data modelers & consumers
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.

5 Likes
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.

5 Likes
3

This article was massively useful for me looking at the user-specific timezone info.

One addition I made was to COALESCE() for those cases where an os_timezone might be missing, and fail-defaulting to UTC. So my statement became:

convert_timezone('UTC', COALESCE(os_timezone, geo_timezone, 'UTC'), derived_tstamp) .

4

One thing to be wary of here. The reported os_timezone and geo_timezone from a device won’t necessarily be in the Redshift timezone database so you may end up with failed queries (or incorrect tz offsets). From memory the timezone database in Redshift is somewhere around 2016f (the latest is 2018f).

1 Like
5

Ah! Yes, good to know - thanks mike.
I am just verifying the data now and I do in fact see some exceptions of this type.