I have data with start and end date-times in POSIXct format. I want to calculate the number of hours between each start and end date-time, omitting the hours between 11 pm and 5 am from the calculation. Here is the sample data:
times <- data.frame(start = c('2021-11-01 04:08:01 EDT', '2021-11-02 08:56:06 EDT', '2021-11-04 08:59:24 EDT', '2021-11-08 17:48:37 EST'),
end = c('2021-11-01 23:44:01 EDT', '2021-11-03 11:04:01 EDT', '2021-11-05 13:25:08 EDT', '2021-11-12 04:56:03 EDT')) %>%
mutate(start = as.POSIXct(start),
end = as.POSIXct(end))
How can I do this? I am not sure how to proceed.
A way could be:
times %>% rowwise() %>%
mutate(
hours = length(seq.POSIXt(start,end, "hour")),
hours_not_11_17 = with(as.POSIXlt(seq.POSIXt(start,end, "hour")),
length(which(!between(hour, 11, 17))))) %>%
ungroup()
# start end hours hours_not_11_17
# <dttm> <dttm> <int> <int>
# 1 2021-11-01 04:08:01 2021-11-01 23:44:01 20 13
# 2 2021-11-02 08:56:06 2021-11-03 11:04:01 27 20
# 3 2021-11-04 08:59:24 2021-11-05 13:25:08 29 20
# 4 2021-11-08 17:48:37 2021-11-12 04:56:03 84 62