Reading Timestamp Data in R from Multiple Time Zones

Converting local date-times from a large dataset with multiple time zones into UTC

The error is due to the timeZone column being a factor instead of a character vector, use stringsAsFactors = FALSE when defining the data.frame to specify timeZone as a character column. You can also avoid any loops by using vectorized functions from the lubridate package:

library(lubridate)

df <- data.frame(
    "localDate" = c("2015-04-20","2016-07-17","2015-08-06"), 
    "localTime" = c("14:00", "14:46", "10:35"), 
    "timeZone" = c("Pacific/Pago_Pago", "Pacific/Saipan", "Pacific/Honolulu"),
    stringsAsFactors = FALSE
)

df$eventDate <- force_tzs(ymd_hm(with(df, paste(localDate, localTime))), tzones = df$timeZone)

df
#>    localDate localTime          timeZone           eventDate
#> 1 2015-04-20     14:00 Pacific/Pago_Pago 2015-04-21 01:00:00
#> 2 2016-07-17     14:46    Pacific/Saipan 2016-07-17 04:46:00
#> 3 2015-08-06     10:35  Pacific/Honolulu 2015-08-06 20:35:00

---

Edit: in case of missing values, check for each row whether it can be converted and if not return NA. Below an example solution using base R:

df <- data.frame(
    "localDate" = c("2015-04-20","2016-07-17","2015-08-06", "2019-01-01", "2019-01-01"), 
    "localTime" = c("14:00", "14:46", "10:35", NA, "00:00"), 
    "timeZone" = c("Pacific/Pago_Pago", "Pacific/Saipan", "Pacific/Honolulu", 
        "Pacific/Honolulu", NA),
    stringsAsFactors = FALSE
)

df$eventDate <- apply(df, 1, function(row) {     
      ifelse(any(is.na(row)), NA_character_,
          format(as.POSIXct(paste(row["localDate"], row["localTime"]), "%Y-%m-%d %H:%M",
              tz = row["timeZone"]), tz = "UTC", usetz = TRUE)
      )
    })
df   
#>    localDate localTime          timeZone               eventDate
#> 1 2015-04-20     14:00 Pacific/Pago_Pago 2015-04-21 01:00:00 UTC
#> 2 2016-07-17     14:46    Pacific/Saipan 2016-07-17 04:46:00 UTC
#> 3 2015-08-06     10:35  Pacific/Honolulu 2015-08-06 20:35:00 UTC
#> 4 2019-01-01      <NA>  Pacific/Honolulu                    <NA>
#> 5 2019-01-01     00:00              <NA>                    <NA>

How to convert UTC timestamps to multiple local time zones in R Data Frame?

You have a problem with a) factors and b) passing vectors. So one solution is to set stringsAsFactors to FALSE and create a function to Vectorize your inputs:

df <- data.frame(..., stringsAsFactors=FALSE)

df$utc_time_stamp <- as.POSIXct(df$utc_time_stamp, format="%Y-%m-%d %H:%M:%S", tz='GMT')

tz_v <- Vectorize(function(x,y) {format(x, tz=y, usetz=TRUE)})

df$new_local_time <- tz_v(df$utc_time_stamp, df$time_zone)
df

Output:

>>>        utc_time_stamp          time_zone           new_local_time
>>> 1 2019-04-28 18:35:26         Asia/Tokyo  2019-04-29 03:35:26 JST
>>> 2 2019-04-28 21:28:58 Australia/Brisbane 2019-04-29 07:28:58 AEST
>>> 3 2019-04-28 18:59:01         Etc/GMT-12  2019-04-29 06:59:01 +12

R extract Date and Time with Timezone

I assume your date is in a string format, i.e d <- "2015-07-24 23:15:07".

You can convert it to date based on your local timezone with as.POSIXct.

> a <- as.POSIXct(d)
> a
[1] "2015-07-24 23:15:07 MYT"

(MYT is my local timezone)

And you can convert the same date-time in another timezone specifying tz, e.g.

> b <- as.POSIXct(b, tz="Japan")
> b
[1] "2015-07-24 23:15:07 JST"

For you can convert them to other time zones by changing the tzone attribute. E.g. I want to convert my first variable with local time to the same timezone as the second one

> attr(a, "tzone") <- "Japan"
> a
[1] "2015-07-25 00:15:07 JST"

(If you want to change to your local time zone, set the tzone to "")

If you want to extract just the time, use format

> format(a, "%H")
[1] "00"

or the full date time

> format(a, "%Y-%m-%d %H:%M:%S")
[1] "2015-07-25 00:15:07"
> format(b, "%Y-%m-%d %H:%M:%S")
[1] "2015-07-24 23:15:07"

R, intraday data, convert datetime to a different timezone

You could use lubridate::force_tz and lubridate::with_tz:

dat <- as.POSIXct("2021-05-01 12:00:00",tz = "UTC")
lubridate::force_tz(dat,tz="CET")
#> [1] "2021-05-01 12:00:00 CEST"
lubridate::with_tz(lubridate::force_tz(dat,tz="UTC"))
#> [1] "2021-05-01 14:00:00 CEST"

How to save time zone information in R

Saving to an .RDS is often the best choice to preserve an object exactly as it’s represented in R. In this case, it preserves timezone without conversion:

saveRDS(data.frame(x = x), tmpfile)
df <- readRDS(tmpfile)

df
#                    x
# 1 2015-06-01 12:00:00

lubridate::tz(df$x)
# [1] "EST"

df$x
# [1] "2015-06-01 12:00:00 EST"

How do you convert dates/times from one time zone to another in R?

First, convert the London time to a POSIXct object:

pb.txt <- "2009-06-03 19:30"
pb.date <- as.POSIXct(pb.txt, tz="Europe/London")

Then use format to print the date in another time zone:

> format(pb.date, tz="America/Los_Angeles",usetz=TRUE)
[1] "2009-06-03 11:30:00 PDT"

There are some tricks to finding the right time zone identifier to use. More details in this post at the Revolutions blog: Converting time zones in R: tips, tricks and pitfalls

---

Related Topics