How to Convert Dates into Different Time Zones in Q

Introduction

Working with dates and times can be challenging at the best of times, let alone when they are in the wrong time zone or format, or you want to manipulate them in some other way. Thankfully this is made easy in Q using an extremely helpful R package called lubridate.

Method

Let’s look at the following scenario. We have imported some data into Q but the date has been stored in UTC time as an ISO-8601 string, e.g. “2018-11-19T16:52:48.000Z”. We know this is the case as the “Z” at the end signifies Zero time zone or UTC while the T is used as a separator between date and time. We want to, however, change the below dates to our local Sydney time zone (AEST). I will now show you how this is possible in Q using R’s lubridate package.

Create an R variable in Q to store the new date as a string by clicking Insert Variable(s) > R Variable.
Paste the below code into the R CODE box on the right to convert the ‘DateTime’ variable from UTC to Sydney AEST time.
Update the Question Name field, press the blue Play button to run it, followed by the Add R Variable to save it to the data set.

library(lubridate)
d = ymd_hms(DateTime)
date = force_tzs(d, tzones = "UTC") 
strftime(date, format = "%Y/%m/%d %H:%M", tz = "Australia/Sydney")

In this code:

We call the lubridate library
We then use the ymd_hms function to convert the date string from ISO format into a conventional date format
As the converted date is now in a recognizable format, we can simply use force_tzs to append the UTC time zone to our date without the need to format it first
The last step is to set it back to a string using strftime in the preferred format with slashes instead of dashes (YYYY/MM/DD HH:MM) but with the specified time zone set to local Sydney time (AEST). A list of the time zone names that dates can be converted to can be found here. The advantage of using lubridate‘s functions instead of arbitrarily adding the current 11-hour difference between time zones is that it automatically takes into account Daylight Saving.

Articles in this section

Introduction

Method

See Also

Comments