In this article, we'll explore how to convert a timestamp or datetime field to another timezone.
All the operations can be done using simple calculated formulas, including:-
- DATETIME_ADD - To add specific time duration to a timestamp.
- DATETIME_SUB - To subtract specific time duration from a timestamp.
Download Dataset
In case you'd love to have some hands-on while learning, you may download my mockup dataset for your practice. It's an e-commerce dataset, so we'll have data like Transaction ID, Customer Name, Ratings, Product Purchased and all the timestamps for each touch points: Order Placed, Paid, Shipped Out and Delivered.
Before we start, this video comprises most of the content on this article, with additional tutorials on how to calculate time difference between two timestamps. You are highly recommend to watch this tutorial to learn more about how to work with dates in Data Studio.
Timezone Conversion in Data Studio
To change timezone for a datetime field in Data Studio, you can use the formula DATETIME_ADD() or DATETIME_SUB() to create a calculated field for the converted timestamps.
Example 1: UTC to GMT+8
For example, if we want to convert a UTC timestamp to another timezone GMT+8 (Kuala Lumpur), we need to add 8 hours to the existing timestamp field. Hence, we'll use datetime_add() formula to add a duration.
DATETIME_ADD(datetime_expression, INTERVAL integer part)To apply this formula to our use case, we'll construct the formula below:
DATETIME_ADD(Order Received, INTERVAL 8 HOUR)
You may notice that in the formula, we'll first indicate the timestamp field, then follow by the "INTERVAL", and lastly indicate the duration to be added to the timestamp.
Example 2: UTC to GMT-5
If you wish to convert timezone from UTC/GMT to another timezone in GMT-5, then you may use DATETIME_SUB() formula to minus time duration (5 hours) from the UTC timestamp.
DATETIME_ADD(datetime_expression, INTERVAL integer part)To apply this formula to our use case, we'll construct the formula below:
DATETIME_ADD(Order Received, INTERVAL 8 HOUR)>
Similar to DATETIME_ADD formula, we'll first indicate the timestamp field, then follow by the "INTERVAL", and lastly indicate the time duration to be deducted from the timestamp.
See More
Do you want to learn more about how to work with dates, datetime or timestamps fields in Google Data Studio / Looker Studio? Below are some of the resources you may want to check out!