Split sequence between two timestamps into several days at midnight

Question

I have a dataset with two datetime columns: Begin & End:

library(tidyverse)

df <- tibble(
  Group = c("A", "B", "C"),
  Begin = as_datetime(c("2023-07-15 01:40:11", "2023-07-22 05:54:44", "2023-08-05 16:43:09")),
  End = as_datetime(c("2023-07-15 13:43:15", "2023-07-25 10:50:45", "2023-08-06 10:42:12"))
)

df
# A tibble: 3 × 3
  Group Begin               End                
  <chr> <dttm>              <dttm>             
1 A     2023-07-15 01:40:11 2023-07-15 13:43:15
2 B     2023-07-22 05:54:44 2023-07-25 10:50:45
3 C     2023-08-05 16:43:09 2023-08-06 10:42:12

The period between these two timestamps can be several days. Begin & End, on the other hand, can also be the same day. Now I want to split this time sequence into several days.
The days between Begin & End are "full" days and therefore start at 00:00:00 and end at 23:59:59.
I think this desired output illustrates what I want exactly:
(I only added the Group column for better illustration and it is not relevant for programming for now).

# A tibble: 7 × 3
  Group Begin               End                
  <chr> <dttm>              <dttm>             
1 A     2023-07-15 01:40:11 2023-07-15 13:43:15
2 B     2023-07-22 05:54:44 2023-07-22 23:59:59
3 B     2023-07-23 00:00:00 2023-07-23 23:59:59
4 B     2023-07-24 00:00:00 2023-07-24 23:59:59
5 B     2023-07-25 00:00:00 2023-07-25 10:50:45
6 C     2023-08-05 16:43:09 2023-08-05 23:59:09
7 C     2023-08-06 00:00:00 2023-08-06 10:42:12

Can anyone help me find a solution?
I think the difficulty is to keep the Begin-timestamp of the first day and the End-timestamp of the last day.

Answer 1

df |>
  # create a date column for beginning and end, for ease of use
  mutate(b = as.Date(Begin), e = as.Date(End),
        # create a sequence of dates between begin and end
        days = map2(b, e, ~ seq.Date(.x, .y, by = "1 day"))) |>
  # unnest the days column into many rows
  unnest(days) |>
  # if the beginning date is the same as the date in `days`, then use the original Begin column
  # else, use `days` as a datetime
  mutate(Begin = if_else(b == days, Begin, as_datetime(days)),
         # same with End, but adding a day, and subtracting a second
         End = if_else(e == days, End, as_datetime(days) + days(1) - seconds(1)), .keep = "unused")

Output:

  Group Begin               End                
  <chr> <dttm>              <dttm>             
1 A     2023-07-15 01:40:11 2023-07-15 13:43:15
2 B     2023-07-22 05:54:44 2023-07-24 23:59:00
3 B     2023-07-22 00:00:00 2023-07-24 23:59:00
4 B     2023-07-22 00:00:00 2023-07-24 23:59:00
5 B     2023-07-22 00:00:00 2023-07-25 10:50:45
6 C     2023-08-05 16:43:09 2023-08-05 23:59:00
7 C     2023-08-05 00:00:00 2023-08-06 10:42:12