R Passing a string vector through tz in POSIXct function

Question

I have two vectors that I am trying to pass through a POSIXct(). GMTDteTmeStamp is my list of times in GMT and TZ is my list of corresponding local timezones that I need the GMT times translated into. I have tried multiple ways of passing TZ through but I am not getting anywhere.

Data:

> GMTDteTmeStamp
 [1] 43138.5 43139.5 43168.5 43169.5 43170.5 43171.5 43172.5 43141.5 43142.5 43143.5 43144.5 43176.5
[13] 43177.5 43178.5
> TZ
 [1] "Europe/London"       "America/New_York"    "America/Chicago"     "America/Chicago"    
 [5] "America/Chicago"     "America/Chicago"     "America/Chicago"     "America/Phoenix"    
 [9] "America/Los_Angeles" "Europe/Madrid"       "America/Santiago"    "America/Sao_Paulo"  
[13] "America/Sao_Paulo"   "America/Sao_Paulo"

Code: (I am trying both with_tz and POSIXct to see if I can get either to work.)

  TZX <- TZDC[,1]
  GMTDteTmeStamp <- (TD[,2]) ## get GMT time to be converted
  GMTDteTmeStamp <- as.double(unlist(GMTDteTmeStamp), options(digits = 6)) 
  GMTDteTmeValue <- as.POSIXct((GMTDteTmeStamp) * 86400
                               , origin = as.POSIXct("1899-12-30", tz = "UTC")
                               , tz = "UTC") ## Force GMT time as GMT 


  TZ <- TZX
  x <- with_tz(GMTDteTmeValue, tz = TZ)
  APLDteTmeValue <- as.POSIXct((GMTDteTmeStamp) * 86400, origin = "1899-12-30", tz = TZ)

I can manually pass the TZ values through so I know there is no issue there. I am getting

Error in as.POSIXlt.POSIXct(x, tz) : invalid 'tz' value

Thanks for the assistance.

score 0 · Accepted Answer · answered Feb 14 '18 at 21:16

So it turns out you can't pass lists or vectors through tz. I also found out that a single column can not have more than one tz associated with it. Not sure why that is, but i did find a work around for anyone having this issue in the future.

I first used a loop to pass the variable time zone name through tz to get the correct local time for the record location. Then I passed that through another variable with force_tz to force the local time with a "GMT" time zone. This allowed my times in my "Local Time" column to read correctly eventhough R thinks they are GMT times.

A note about the Local Time column. Make sure you set is as a .POSIXct(.., tz = "UTC") or the column will translate all your times to system local time.

Here is the final code that translates the GMT times to record Local time. Note that I have a data file with all the Airport codes and corresponding time zone names.

library(readxl)
library(data.table)
library(plyr)
library(tidyr)
library(dplyr)
library(tibble)
library(fastmatch)
library(stringr)
library(magrittr)
library(RcppBDT)
library(lubridate)
library(openxlsx)
##library(anytime)

## Load time zone data sheet
TZData  <- read_excel("TZDataFile.xlsx")
TZData <- as.data.table(TZData)
TZRange <- TZData[,1]
TZRange <- as.data.frame(TZRange)

##Bring in test data
TD  <- read_excel("Test dates.xlsx", col_types = c("text", "text"))
TD <- as.data.table(TD)

####Start Time Conversion Code####

## Define variables
Station <- TD[,1] ##Station
GMT <- TD[,2] ##Date/time stamp in GMT to be converted to local
z <- nrow(TD)+0

STA <- as.data.frame(Station[,1]) ## Get Station
APCode <- fmatch(STA[,1],TZRange[,1]) ## Match station on Time Zone Data sheet
TZDC <- as.data.frame(TZData[APCode,3])
TZX <- TZDC[,1]
GMTDteTmeStamp <- (TD[,2]) ## get GMT time to be converted
GMTDteTmeStamp <- as.double(unlist(GMTDteTmeStamp), options(digits = 6))
GMTDteTmeValue <- as.POSIXct((GMTDteTmeStamp) * 86400
                             , origin = as.POSIXct("1899-12-30", tz = "UTC")
                             , tz = "UTC") ## Force GMT time as GMT
TD <- add_column(TD,GMTDteTmeValue)
LT <- .POSIXct(double(z), tz = "UTC")
TD <- add_column(TD, LT )

for (i in 1:z){
  TZ <- TZDC[i,1]
  LclDteTmeValue <- with_tz(GMTDteTmeValue[i], TZ)
  LclGMT <- force_tz(LclDteTmeValue, tzone = "UTC" )
  TD[i,4] <-LclGMT
}

 > TD
    Sta          GMT Dept      GMTDteTmeValue                  LT
 1: LHR 43183.51666666667 2018-03-24 12:24:00 2018-03-24 12:24:00
 2: LHR 43184.51666666667 2018-03-25 12:24:00 2018-03-25 13:24:00
 3: PHL 43169.51666666667 2018-03-10 12:24:00 2018-03-10 07:24:00
 4: PHL 43170.51666666667 2018-03-11 12:24:00 2018-03-11 08:24:00
 5: DFW 43169.51666666667 2018-03-10 12:24:00 2018-03-10 06:24:00
 6: DFW 43170.51666666667 2018-03-11 12:24:00 2018-03-11 07:24:00
 7: PHX 43169.51666666667 2018-03-10 12:24:00 2018-03-10 05:24:00
 8: PHX 43170.51666666667 2018-03-11 12:24:00 2018-03-11 05:24:00
 9: LAX 43169.51666666667 2018-03-10 12:24:00 2018-03-10 04:24:00
10: LAX 43170.51666666667 2018-03-11 12:24:00 2018-03-11 05:24:00
11: BCN 43183.51666666667 2018-03-24 12:24:00 2018-03-24 13:24:00
12: BCN 43184.51666666667 2018-03-25 12:24:00 2018-03-25 14:24:00
13: SCL 43317.51666666667 2018-08-05 12:24:00 2018-08-05 08:24:00
14: SCL 43318.51666666667 2018-08-06 12:24:00 2018-08-06 08:24:00
15: GIG 43176.51666666667 2018-03-17 12:24:00 2018-03-17 09:24:00
16: GIG 43177.51666666667 2018-03-18 12:24:00 2018-03-18 09:24:00

R Passing a string vector through tz in POSIXct function

1 Answers1