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.

Data sets often contain dates that a record was created, amended or a specific event took place. However, depending on how the data was collected, the date format may not be ideal. Sometimes the date may be in a different format (i.e., month–day–year instead of day/month/year). And sometimes it may be stored in UTC (Coordinated Universal Time) or the hosted server time zone instead of the local time set on your computer. Luckily there are ways to convert dates into different time zones in Q without a lot of hassle.

Scenario

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.

Using the lubridate R package in Q

First, we will create an R variable in Q to store the new date as a string by clicking Insert Variable(s) > R Variable. Next, we will paste the below code into the R CODE box on the right to convert the 'DateTime' variable from UTC to Sydney AEST time. Then we 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:

  1. We call the lubridate library
  2. We then use the ymd_hms function to convert the date string from ISO format into a conventional date format
  3. 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
  4. 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.

 

Set as Date/Time

Finally, to turn this from a text variable into a date variable, we go to Variables and Questions tab and change the Variable Type to Date/Time. Now we have a new date variable in our local time.

Find out how to do more in Q!