How to create datetime object from character datetime and timezone columns?-CodePudding

I have a dataframe with dates saved in one column and the corresponding time zone in another, both in character format. The dates are local dates in the respective timezone noted in the timezone column, not UTC.

data_frame = data.frame(eventid = c(1:4),
                        start = c("2021-05-05 01:04:34",
                                  "2021-03-06 03:14:44",
                                  "2021-03-11 07:22:48",
                                  "2021-02-02 11:54:56") ,
                        start_timezone = c("Europe/Berlin",
                                         "Europe/Berlin",
                                         "Europe/Berlin",
                                         "Indian/Maldives")
)

I need to convert the date column into datetime objects that take the differing timezones into account, but feeding the text timezone to any function has not worked for me, e.g.

data_frame %>%
  rowwise() %>%
  mutate(start_in_zone= as.POSIXct(start, tz = start_timezone))

How would I approach this ?

CodePudding user response：

Using the lubridate package, you need first to transform your data as date, then force the timezone with with_tz()

library(lubridate)
library(dplyr)


df %>% 
  mutate(l_start = ymd_hms(start)) %>% 
  group_by(start_timezone) %>% 
  mutate(start_local = with_tz(l_start, start_timezone)) %>% 
  select(-l_start)


# A tibble: 4 x 4
# Groups:   start_timezone [2]
  eventid start               start_timezone  start_local        
    <int> <chr>               <chr>           <dttm>             
1       1 2021-05-05 01:04:34 Europe/Berlin   2021-05-05 03:04:34
2       2 2021-03-06 03:14:44 Europe/Berlin   2021-03-06 04:14:44
3       3 2021-03-11 07:22:48 Europe/Berlin   2021-03-11 08:22:48
4       4 2021-02-02 11:54:56 Indian/Maldives 2021-02-02 12:54:56

CodePudding user response：

You may use as_datetime function to change the time to UTC.

library(dplyr)
library(lubridate)

data_frame %>%
  rowwise() %>%
  mutate(UTC_time= as_datetime(start, tz = start_timezone) %>% 
                        as_datetime(tz = 'UTC')) %>%
  ungroup

#   eventid start               start_timezone  UTC_time           
#    <int> <chr>               <chr>           <dttm>             
#1       1 2021-05-05 01:04:34 Europe/Berlin   2021-05-04 23:04:34
#2       2 2021-03-06 03:14:44 Europe/Berlin   2021-03-06 02:14:44
#3       3 2021-03-11 07:22:48 Europe/Berlin   2021-03-11 06:22:48
#4       4 2021-02-02 11:54:56 Indian/Maldives 2021-02-02 06:54:56