Skip to content

Instantly share code, notes, and snippets.

@shermozle

shermozle/Timezone conversion in Snowplow

Created April 16, 2013 02:40
Show Gist options
  • Save shermozle/5392938 to your computer and use it in GitHub Desktop.
Save shermozle/5392938 to your computer and use it in GitHub Desktop.
Download ZIP
Let the database do the work of timezone conversion. Mainly because Tableau isn't great at this. This is Postgres. Timezone names from http://en.wikipedia.org/wiki/Zone.tab
Raw
Timezone conversion in Snowplow
"events"."collector_tstamp" AT TIME ZONE 'Australia/Sydney' AS "collector_tstamp AEST"
@kingo55
Copy link

kingo55 commented Dec 8, 2013

Hey Simon,

Are you sure this works? I think it needs to be:

SET TIME ZONE 'Australia/Melbourne';

SELECT collector_tstamp AT TIME ZONE 'UTC'
FROM atomic.events
LIMIT 1;

When you use AT TIME ZONE, it set's the timezone of the data (there isn't a TZ attached to the Snowplow data). Then, whatever you have your timezone set to, SP will automatically show it in your local time.

I ran the following query to check the latest events in my Snowplow DB:

SELECT
MAX("collector_tstamp") AT TIME ZONE 'UTC' AS "collector_tstamp UTC",
MAX("collector_tstamp") AT TIME ZONE 'AEST' AS "collector_tstamp AEST"
FROM
"atomic".events;

Here's what it pulled for each column (note my Snowplow job runs each day at 12pm AEST):

2013-12-08 10:39:55+11 2013-12-08 00:39:55+11

As you can see, the first value is closest to the run date - which makes sense.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment