Rounding time to specific hour even if it is the day before

user_15462168 0
Viewed 51

I have time data that I would like to subtract from twilight essentially, which I have other code for. To do that, I first need to standardize the times in relation to the nearest twilight. What I am having trouble doing is rounding times AFTER midnight, back to twilight in the previous day (see: Df$time[2])

I've used the lubridate function round_date with a period() in it successfully for times on the same day, but for early morning times it just reverts to midnight of said day. The "change_on_boundary" argument in the help menu does not seem to function anymore. Anyone have another way of doing this?

*note: I know the tz changed, I'm not worried about that. This is a dummy dataset for this question.

Df<-data.frame(time = as.POSIXct(c("2020-12-29 21:02:23 UTC", "2020-12-15 00:48:21 UTC", "2020-12-09 21:55:52 UTC" ,"2020-12-09 18:40:06 UTC",
 "2020-12-08 18:25:27 UTC" ,"2020-12-14 20:08:51 UTC", "2020-12-14 17:55:21 UTC","2020-12-05 23:53:36 UTC",
 "2020-12-05 21:21:21 UTC", "2020-12-06 19:11:11 UTC", "2020-12-05 21:09:31 UTC", "2020-12-05 18:13:53 UTC",
 "2020-12-15 20:36:56 UTC", "2020-12-26 06:00:00 UTC", "2020-12-16 00:40:46 UTC" ,"2020-12-26 05:34:42 UTC")))

Df$time
 [1] "2020-12-29 21:02:23 EST" "2020-12-15 00:48:21 EST" "2020-12-09 21:55:52 EST" "2020-12-09 18:40:06 EST" "2020-12-08 18:25:27 EST"
 [6] "2020-12-14 20:08:51 EST" "2020-12-14 17:55:21 EST" "2020-12-05 23:53:36 EST" "2020-12-05 21:21:21 EST" "2020-12-06 19:11:11 EST"
[11] "2020-12-05 21:09:31 EST" "2020-12-05 18:13:53 EST" "2020-12-15 20:36:56 EST" "2020-12-26 06:00:00 EST" "2020-12-16 00:40:46 EST"
[16] "2020-12-26 05:34:42 EST"

round_date(Df$time, unit = period(16, units = "hour"))
 [1] "2020-12-29 16:00:00 EST" "2020-12-15 00:00:00 EST" "2020-12-09 16:00:00 EST" "2020-12-09 16:00:00 EST" "2020-12-08 16:00:00 EST"
 [6] "2020-12-14 16:00:00 EST" "2020-12-14 16:00:00 EST" "2020-12-05 16:00:00 EST" "2020-12-05 16:00:00 EST" "2020-12-06 16:00:00 EST"
[11] "2020-12-05 16:00:00 EST" "2020-12-05 16:00:00 EST" "2020-12-15 16:00:00 EST" "2020-12-26 00:00:00 EST" "2020-12-16 00:00:00 EST"
[16] "2020-12-26 00:00:00 EST"
lubridate r time
1 Answers

If I understand the question correctly, you want to round each time to the nearest 16:00 (4 pm). Like you have found, lubridate::round_date() seems to stop at midnight when rounding back in time.

For your example, you could find which rounded datetimes have hit the floor of midnight, and subtract 8 hours to get to 16:00 the previous day.

library(tidyverse)
library(lubridate)

Df %>% 
  tibble %>% 
  force_tz('UTC') %>% 
  mutate(
    time_round = round_date(time, unit = period(16, units = 'hour')),
    time_round_final = as_datetime(ifelse(hour(time_round) == 0,       # if the hour is 0 (midnight), then...
                                     time_round - hours(24 - 16),      # we subtract 8 (24 - 16) hours, otherwise...
                                     time_round))                      # we keep the original rounded time
  )

# A tibble: 16 x 3
#    time                time_round          time_round_final   
#    <dttm>              <dttm>              <dttm>             
#  1 2020-12-29 21:02:23 2020-12-29 16:00:00 2020-12-29 16:00:00
#  2 2020-12-15 00:48:21 2020-12-15 00:00:00 2020-12-14 16:00:00
#  3 2020-12-09 21:55:52 2020-12-09 16:00:00 2020-12-09 16:00:00
#  4 2020-12-09 18:40:06 2020-12-09 16:00:00 2020-12-09 16:00:00
#  5 2020-12-08 18:25:27 2020-12-08 16:00:00 2020-12-08 16:00:00
#  6 2020-12-14 20:08:51 2020-12-14 16:00:00 2020-12-14 16:00:00
#  7 2020-12-14 17:55:21 2020-12-14 16:00:00 2020-12-14 16:00:00
#  8 2020-12-05 23:53:36 2020-12-05 16:00:00 2020-12-05 16:00:00
#  9 2020-12-05 21:21:21 2020-12-05 16:00:00 2020-12-05 16:00:00
# 10 2020-12-06 19:11:11 2020-12-06 16:00:00 2020-12-06 16:00:00
# 11 2020-12-05 21:09:31 2020-12-05 16:00:00 2020-12-05 16:00:00
# 12 2020-12-05 18:13:53 2020-12-05 16:00:00 2020-12-05 16:00:00
# 13 2020-12-15 20:36:56 2020-12-15 16:00:00 2020-12-15 16:00:00
# 14 2020-12-26 06:00:00 2020-12-26 00:00:00 2020-12-25 16:00:00
# 15 2020-12-16 00:40:46 2020-12-16 00:00:00 2020-12-15 16:00:00
# 16 2020-12-26 05:34:42 2020-12-26 00:00:00 2020-12-25 16:00:00

I used lubridate::force_tz() to apply a timezone to the times (which was empty), otherwise as_datetime() seems to default to UTC (it must assume the timezone-less times are your local timezone?) and returns unexpected times, by silently changing (applying) the timezone. This can be a common cause of error.

We can observe this when we don't force the timezone (I am in GMT+11):

Df %>% 
  tibble %>% 
  # force_tz('UTC') %>%
  mutate(
    time_round = round_date(time, unit = period(16, units = 'hour')),
    time_round_final = as_datetime(ifelse(hour(time_round) == 0,       # if the hour is 0 (midnight), then...
                                          time_round - hours(24 - 16),      # we subtract 8 (24 - 16) hours, otherwise...
                                          time_round)),                      # we keep the original rounded time
    tz_1 = tz(time), 
    tz_2 = tz(time_round),
    tz_3 = tz(time_round_final),
  )

# # A tibble: 16 x 6
#    time                time_round          time_round_final    tz_1  tz_2  tz_3 
#    <dttm>              <dttm>              <dttm>              <chr> <chr> <chr>
#  1 2020-12-29 21:02:23 2020-12-29 16:00:00 2020-12-29 05:00:00 ""    ""    UTC  
#  2 2020-12-15 00:48:21 2020-12-15 00:00:00 2020-12-14 05:00:00 ""    ""    UTC  
#  3 2020-12-09 21:55:52 2020-12-09 16:00:00 2020-12-09 05:00:00 ""    ""    UTC  
#  4 2020-12-09 18:40:06 2020-12-09 16:00:00 2020-12-09 05:00:00 ""    ""    UTC  
#  5 2020-12-08 18:25:27 2020-12-08 16:00:00 2020-12-08 05:00:00 ""    ""    UTC  
#  6 2020-12-14 20:08:51 2020-12-14 16:00:00 2020-12-14 05:00:00 ""    ""    UTC  
#  7 2020-12-14 17:55:21 2020-12-14 16:00:00 2020-12-14 05:00:00 ""    ""    UTC  
#  8 2020-12-05 23:53:36 2020-12-05 16:00:00 2020-12-05 05:00:00 ""    ""    UTC  
#  9 2020-12-05 21:21:21 2020-12-05 16:00:00 2020-12-05 05:00:00 ""    ""    UTC  
# 10 2020-12-06 19:11:11 2020-12-06 16:00:00 2020-12-06 05:00:00 ""    ""    UTC  
# 11 2020-12-05 21:09:31 2020-12-05 16:00:00 2020-12-05 05:00:00 ""    ""    UTC  
# 12 2020-12-05 18:13:53 2020-12-05 16:00:00 2020-12-05 05:00:00 ""    ""    UTC  
# 13 2020-12-15 20:36:56 2020-12-15 16:00:00 2020-12-15 05:00:00 ""    ""    UTC  
# 14 2020-12-26 06:00:00 2020-12-26 00:00:00 2020-12-25 05:00:00 ""    ""    UTC  
# 15 2020-12-16 00:40:46 2020-12-16 00:00:00 2020-12-15 05:00:00 ""    ""    UTC  
# 16 2020-12-26 05:34:42 2020-12-26 00:00:00 2020-12-25 05:00:00 ""    ""    UTC
user_134787490
Related