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.
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.
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.
How to Create Date Variables in Q
How to Create Filters Using Date Questions
How to Calculate the Duration Between Two Dates
How to Set Time Periods for Date Questions
Article is closed for comments.