CODEWITHSUNDEEP
google-bigquerylooker-studio
Steven Roth
Steven Roth

Reputation: 23

How to show date/time in local time zone in Data Studio from UTC timestamp in Big Query

I want Google Data Studio reports to show sales data (including a sales-by-hour heat map report) using the user's local time zone. I'm storing the data in BigQuery and the timestamp field is stored as UTC.

Per Data Studio Help, it sounds like the timestamp should be stored as UTC (which I'm doing). I assumed Data Studio was smart enough to convert UTC to the user's local time zone, but that doesn't appear to be the case.

Everything is working perfectly except the hours on the heat map report show as 8:00-17:00 instead of 12:00-21:00. It's showing UTC instead of EST (my time zone), and I cannot figure out how to fix this.

Does Data Studio automatically adjust the report data based on the user's time zone? If so, what am I doing wrong? If not, are there any workarounds that would support users from multiple time zones?

Upvotes: 2

Views: 5612

Answers (3)

Nimantha
Nimantha

Reputation: 6472

The 17 Sep 2020 Update to Google Data Studio introduced updates to Dates and Times as well as new functions and ways to works with Dates and Times which includes Time Zones.

0) Upgrade the Date Field

Ensure that the Date Time field has been upgraded to to the newer Date Time field type.

Added a GIF to elaborate:

1) EST

This Calculated Field obtains the difference in SECOND between UTC and EST by using the DATETIME_DIFF function, and subsequently subtracts the difference with the Date Time field (called DateTimeField in this Report):

PARSE_DATETIME(
    "%s",
    CAST(CAST(FORMAT_DATETIME("%s",DateTimeField)AS NUMBER) - DATETIME_DIFF(CURRENT_DATETIME("UTC"),CURRENT_DATETIME("EST"), SECOND)AS TEXT))

Google Data Studio Report and a GIF to elaborate:

Upvotes: 3

lwg
lwg

Reputation: 1

In my case the solution was convert the time to MICRO and then add 1 hour(MICRO).

TODATE(((name_of_column)+(3600000000)),'MICROS','%Y%m%d%H%M')

Upvotes: -1

Amanda Nguyen
Amanda Nguyen

Reputation: 26

You can use the second argument of the TIMESTAMP() function to convert the UTC timestamp to a specific time zone. Here's a list of time zones supported by Big Query. 

ex. SELECT TIMESTAMP("2008-12-25 15:30:00", "America/New_York") AS timestamp_in_est;

+-------------------------+
| timestamp_in_est        |
+-------------------------+
| 2008-12-25 20:30:00 UTC | 
+-------------------------+

Data Studio does not adjust timestamps based on timezones. All timestamps are displayed in UTC unless specified.

Upvotes: 1

Related Questions