How to convert whole column of dates/times from one time zone to another in R?

Question

Every row has a different origintimezone. I need to make use of the standardformat_new column which is in the origintimezone time to create a new column where the time follows the com_timezonenames.

A simplified version would be something like this

a = ymd_hms('2017-10-10 10:20:00',tz = 'Australia/Sydney')
a
with_tz(a, tz = 'Singapore')

[![enter image description here][2]][2]

However, when I'm applying a similar code to that above, I am met with the following error.

[![enter image description here][3]][3]

I have tried making a for loop to run the whole list but the dataset of over a few hundred thousand makes it unfeasible.

REPREX as reqeusted This is what i have initially.

[![enter image description here][4]][4]

structure(list(com_country = c("SG", "AU", "NZ", "UK", "AU", 
"AU"), orderdate_local = c("9/20/2017 4:47:35 AM", "9/30/2017 7:00:00 AM", 
"9/20/2017 1:37:14 AM", "9/18/2017 9:38:34 PM", "9/30/2017 3:07:29 AM", 
"9/30/2017 10:17:01 AM"), origincountry = c("US", "AU", "UK", 
"AU", "AU", "AU")), .Names = c("com_country", "orderdate_local", 
"origincountry"), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame"))

orderdate_local is given in the origincountry timezone so i need to include map the country code over to the tz value.

##getting origin timezone from countrycode
dd$origintimezone <- mapvalues(dd$origincountry, 
                                      from=c("AU", "UK", "US"), 
to=c("Australia/Sydney","Europe/London","America/Toronto"))

thereafter i need to convert orderdate_local into the standard POSIXlt format so i can use the strptime function

dd$standardformat_new <- strptime(dd$orderdate_local,format="%m/%d/%Y 
%I:%M:%S %p")
dd$com_timezonenames <- mapvalues(dd$com_country, 
                      from=c("AU","MY","NZ","SG","PH","HK","UK","TH"), 
                              to=c("Australia/Sydney", "Asia/Kuala_Lumpur", "Pacific/Auckland","Asia/Singapore","Asia/Manila","Asia/Hong_Kong","Europe/London","Asia/Bangkok"))

This is what we have as of now.

De Novo · Answer 1 · 2018-03-05T09:55:29.087

EDITED after trying several options with your reprex and running into a invalid 'tz' value when trying to force it to accept different time zones for different elements of a date time vector, I dug through the documentation to find this:

R date-time vectors cannot hold elements with non-uniform time zones

It looks like you'll have to store the date time in UTC time, which is really frustrating.

Here's how you'd do that:

dd$standardformat_new <- force_tzs(mdy_hms(dd$orderdate_local), 
                                   tzones = dd$com_timezonenames)

The above code uses the lubridate package to both convert your nonstandard character vector representing the date and time to a date-time object, takes as an input the Olson Name timezones you generated, and produces a vector of the UTC times corresponding to the local times in the specified timezones. This is unfortunately not as nice and readable, but it does have the advantage of accurately representing the relative times between time zones and allowing you to perform further computations on the vector as a date-time object. You can, of course, choose to display the data with a different time zone (just set tzone_out to some other time zone), but it has to be the same time zone for all of the values.

And thanks for putting together that reprex. I hope this was somewhat helpful

Thanks for the help but it takes a very long time for the whole function to run. I just added the reprex above, many thanks if you could help :) — teo93, Mar 05 '18 at 08:34

score 0 · Answer 2 · answered Mar 07 '18 at 02:39

        ##getting origin timezone from countrycode
        dd$origintimezone <- mapvalues(dd$origincountry, 
                               from=c("AU", "UK", "US"), 

        to=c("Australia/Sydney","Europe/London","America/Toronto"))

        ##getting com_country timezone 
        dd$com_timezonenames <- mapvalues(dd$com_country, 

        from=c("AU","MY","NZ","SG","PH","HK","UK","TH"), 
                                  to=c("Australia/Sydney", 
        "Asia/Kuala_Lumpur","Pacific/Auckland","Asia/Singapore","Asia/Manila","Asia/Hong_Kong","Europe/London","Asia/Bangkok"))




         dd$origintimezoneTIME <- force_tzs(mdy_hms(dd$orderdate_local),tzones = 
         dd$origintimezone,tzone_out = "UTC")
         dd_table <- data.table(A = 1:nrow(dd),
                       origintimezoneTIME_UTC = dd$origintimezoneTIME,
                       com_timezonenames = dd$com_timezonenames,
                       com_country = dd$com_country,
                       orderdate_local = dd$orderdate_local,
                       origincountry = dd$origincountry,
                       origintimezone = dd$origintimezone)

       dd_table[ , com_countryTIME := format(toTz(origintimezoneTIME_UTC, "UTC", 
       com_timezonenames), tz=com_timezonenames), by=A ]

Code with help from this page Adjusting UTC date/time to different time zones by reference in lubridate

score 0 · Answer 3 · answered Mar 07 '18 at 09:29

dl%>%
  mutate(time1 = as.POSIXct(strptime(orderdate_local,format="%m/%d/%Y %I:%M:%S %p",tz="GMT")),
         time2 = ymd_hms(unlist(lapply(seq(length(time1)),
                                       function(x) {format(with_tz(force_tz(time1[x], com_timezonenames[x]),"GMT"), "%F %T")}))),
         time3 = ymd_hms(unlist(lapply(seq(length(time2)), 
                                       function(x) {format(with_tz(time2[x], origintimezone[x]), "%F %T")}))))

another simpler solution without any fancy library

How to convert whole column of dates/times from one time zone to another in R?

3 Answers3

Linked