I have this database of time stamps (AlertTime), and I know what time zone these are in (TimeZone). I know how to set these date to POSIXCT or if they were all UTC, but I'm struggling to get them identified as their local time stamps because most functions don't accept a vector for tz.
I do need both the local time stamp properly formatted (AlertTimeLocal) and the UTC equivalent (AlertTimeUTC).
AlertTime TimeZone AlertTimeLocal (desired) AlertTimeUTC (desired)
11 May 2020, 06:22 PM America/Denver 2020-05-11 18:22:00 MDT 2020-05-12 00:22:00 MDT
11 MAY 2020, 04:11 AM America/Los_Angeles 2020-05-11 04:11:00 PDT 2020-05-11 11:11:00 UTC
10 MAY 2020, 03:38 PM America/Chicago 2020-05-10 15:38:00 CDT 2020-05-10 20:38:00 CDT
I was using this code but it doesn't seem to do anything anymore:
FreshAir$AlertTimeLocal <- mapply(function(x,y) {format(x, tz=y, usetz=TRUE)}, FreshAir$AlertTime, FreshAir$TimeZone)
Would a hacky solution be to set all the RAW time stamps to UTC, then convert them to the equivalent time zone in the other direction?
We can use force_tzs from lubridate
library(lubridate)
library(dplyr)
df1 %>%
mutate(AlertTimeLocal = dmy_hm(AlertTime),
AlertTimeUTC = force_tzs(AlertTimeLocal, tzones = TimeZone))
# AlertTime TimeZone AlertTimeLocal AlertTimeUTC
#1 11 May 2020, 06:22 PM America/Denver 2020-05-11 18:22:00 2020-05-12 00:22:00
#2 11 MAY 2020, 04:11 AM America/Los_Angeles 2020-05-11 04:11:00 2020-05-11 11:11:00
#3 10 MAY 2020, 03:38 PM America/Chicago 2020-05-10 15:38:00 2020-05-10 20:38:00
If we need to store as separate time zones, we can use a list column
library(purrr)
df2 <- df1 %>%
mutate(AlertTime2 = dmy_hm(AlertTime),
AlertTimeUTC = force_tzs(AlertTime2, tzones = TimeZone),
AlertTimeLocal = map2(AlertTime2, TimeZone, ~ force_tz(.x, tzone = .y)))
df2$AlertTimeLocal
#[[1]]
#[1] "2020-05-11 18:22:00 MDT"
#[[2]]
#[1] "2020-05-11 04:11:00 PDT"
#[[3]]
#[1] "2020-05-10 15:38:00 CDT"
df1 <- structure(list(AlertTime = c("11 May 2020, 06:22 PM",
"11 MAY 2020, 04:11 AM",
"10 MAY 2020, 03:38 PM"), TimeZone = c("America/Denver",
"America/Los_Angeles",
"America/Chicago")), class = "data.frame", row.names = c(NA,
-3L))