check if in GMT, then convert to local time in in GMT - R - looping

Question

I've found a million posts about converting from GMT to local time, but not one that first checks if the date-time is in GMT, and then converting it to its representative local time. As folks are suggesting a duplicate, I am looking to convert from GMT to local time, but more importantly, specifically want to check whether or not a conversion needs to be made. I'm using lubridate, dplyr, zoo, and stringr.

Take this example data (my data is in a larger data frame of nums and POSXIct formats):

for store 1000, the df dataframe has the column Time:

Time<-c("2017-02-02 06:05:00 GMT", "2017-02-04 00:06:10 GMT", "2017-02-05 00:06:15 GMT")
df<-as.data.frame(Time)

time_zone_info is as follows:

x<-1000
timezone<-"US/Eastern"
time_zone_info<-cbind(x,timezone)
time_zone_info<-as.data.frame(time_zone_info)

my current attempt is below:

correct_to_local_time<-function(df){     
    if("GMT" %in% df$Time){
        df$Time<-as.POSIXct(df$Time, tz ="GMT",usetz=TRUE) 
        df$Time<-with_tz(df$Time,tz=time_zone_info$timezone)
        }
    else{
           as.POSIXct(df$Time)}
}
correct_to_local_time(df)

which gives me the following which is computationally incorrect (it should be 1:05:00EDT, 01:10:00EDT, etc., but it did change the GMT to EDT, so I'm quite confused...):

Time
2017-02-02 06:05:00 EDT
2017-02-02 06:10:00 EDT
2017-02-02 06:10:00 EDT

Ideally I'd like something like:

 Local Time        
2017-02-02 01:05:00 EDT
2017-02-04 01:10:00 EDT
2017-02-05 01:15:00 EDT

Once a conversion from GMT to EDT or MST is made, are daylight savings accounted for?

Thanks in advance ya'll

Answer 1

The package lubridate probably can help you.

 require(lubridate)

dfTime<-c("2017-02-02 06:05:00 GMT", 
          "2017-02-04 00:06:00 New_York",
          "2017-02-05 00:06:00 US/Mountain")
dfStore <-c(1000, 2000, 3000)
dftimeZone<-c("US/Eastern", "US/Eastern", "US/Mountain")

time_zone_info <- data.frame(Store = dfStore,
                             Time = dfTime,
                             Timezone = dftimeZone,
                             stringsAsFactors = FALSE)

First, check that all your time zones are within your system's recognized list of time zones. (I wasn't sure.)

all(time_zone_info$Timezone %in% OlsonNames())

Ok, good.

Next, convert your character string of times into POSIXct. I like the lubridate function "ymd_hms" here because it's so flexible. You can also set what the time zone was for that, too, using the argument "tz". This returns the time IN YOUR CURRENT TIME ZONE. For example, I'm in Seattle, so this returns the equivalent time in PST for me. I'm pretty sure lubridate accounts for daylight savings time. (I'm having difficulty getting this to sapply and thus opting for a "for" loop. I'm sure someone else could make this happen without that, though.)

time_zone_info$Time2 <- as.POSIXct(NA)
for(i in 1:nrow(time_zone_info)){
      time_zone_info$Time2[i] <- ymd_hms(time_zone_info$Time[i], 
                                         tz = time_zone_info$Timezone[i])
}

Everything in time_zone_info$Time2 will be in the same time zone, and I believe that's by design for making easy comparisons. It's hard to get R to make multiple time zones in the same column of a data.frame.

Answer 2

Let's start by making a data.frame that's a little closer to what you're actually working with and also a little easier to work with than a one column data.frame that you submit one row of to your function.

df <- data.frame(
      Store = c(1000, 2000, 3000),
      Time = c("2017-02-02 06:05:00 GMT",
               "2017-02-04 00:06:10 GMT",
               "2017-02-05 00:06:15 GMT"),
      stringsAsFactors = FALSE)

Similarly, let's write the other data.frame more concisely and make the classes character strings rather than factors just because factors can trip you up. (This is more a preference than a requirement.)

time_zone_info <- data.frame(
      Store = 1000,
      timezone = "US/Eastern",
      stringsAsFactors = FALSE)

One problem with your function is that you're using the wrong syntax to check whether the characters "GMT" are in your character string. You don't want %in%; you want something like stringr's handy str_detect function.

Also, rather than inputting a data.frame and outputting a data.frame, which can be tricky as you've currently applied it, let's design the function so that the input is a single string and the output is a single string, which is easier to work with.

require(stringr)
require(lubridate)

correct_to_local_time<-function(InputTime){     
      if(str_detect(InputTime, "GMT")){
            GMTTime <- as.POSIXct(InputTime, tz = "GMT", usetz = TRUE) 
            OutputTime <- with_tz(GMTTime, tz = time_zone_info$timezone)
      } else {
            OutputTime <- as.POSIXct(InputTime)}

      return(OutputTime)
}
correct_to_local_time(df$Time[1])

If I were you, I'd also rigorously check that the else portion of your function is returning what you want; I could see that not working with non-standardized time zone info.