Timezones

This tutorial goes over everything you need to know about how timezones are treated in Direct Access. After reading this, you should know how to understand the differences between client_date, client_time, client_time_utc, and device_timezone.

App timezone vs UTC vs Device timezone

In Direct Access, the client_date (datestamp) and client_time (timestamp) fields are stored according to your app’s designated timezone. This is what you set in Localytics when you integrate, and is the timezone in which data is displayed in your Localytics dashboard.

If you want to view timestamps in UTC, you can use client_time_utc. Note that, due to historical reasons, the “timezone” portion of this timestamp (occuring after the “+” sign), is not relevant and should be ignored.

For example, if client_time_utc=2018-03-02 02:47:33.000 +4000, you would ignore the +4000 portion.

The device_timezone is the local timezone offset of the user’s device. If you want to understand the time the user performed the event / had a session according to their timezone, you could use timestampadd(SECOND, device_timezone, client_time_utc)

Note that, for optimal query performance, you should always include a filter on client_date even if you are would like to additionally include the other timestamp fields in your WHERE clause.

Examples

Session counts by hour in UTC:

SELECT date_part(hour, client_time_utc)
, count(*) as total_sessions
FROM fact_sessions
WHERE app_id =  41472
AND client_date BETWEEN '2018-03-01' AND '2018-03-03' 
group by 1;

Session counts by hour in local device timezone:

SELECT date_part(hour, timestampadd(SECOND, device_timezone, client_time_utc)) as utc_time
, count(*) as total_sessions
FROM fact_sessions
WHERE app_id = 41472
AND client_date BETWEEN '2018-03-01' AND '2018-03-03' 
group by 1;

Session counts by hour in app timezone:

SELECT date_part(hour, client_time) as app_time
, count(*) as total_sessions
FROM fact_sessions
WHERE app_id = 41472
AND client_date BETWEEN '2018-03-01' AND '2018-03-03' 
group by 1;

Note that we’re always adding client_date to the WHERE clause in the above examples, for optimal performance.

Note for self-service customers

The Snowflake Web Console will automatically try and transform timestamp fields on display based your account’s default timezone, which will result in incorrect values for client_time_utc and client_time. To fix this, ask your Admin to run:

ALTER ACCOUNT SET TIMEZONE = ‘UTC’;