Looking for a simple way to shift times to remove daylight savings

[Ajackson]

DST problem

I have data containing date-time for a local timezone. Of course the times shift for daylight savings, so that I get time gaps in the Spring and duplicated times in the Fall. This causes an issue when I use date-time in my pivot_wider. I can see a fairly ugly way to fix this, but was hoping someone might have a cleaner way to deal with it.

Before the pivot, for the Fall data,

   Date    Time   Value Type  DateTime   DST   NewDate            
 6 11/07/2021 00:30  0.077 A     2021-11-07 00:30:00 TRUE  2021-11-07 05:30:00
 7 11/07/2021 01:00  0.096 A     2021-11-07 01:00:00 FALSE 2021-11-07 07:00:00
 8 11/07/2021 01:30  0.107 A     2021-11-07 01:30:00 FALSE 2021-11-07 07:30:00
 9 11/07/2021 02:00  0.07  A     2021-11-07 02:00:00 FALSE 2021-11-07 08:00:00
10 11/07/2021 01:30  0.125 A     2021-11-07 01:30:00 FALSE 2021-11-07 07:30:00
11 11/07/2021 02:00  0.071 A     2021-11-07 02:00:00 FALSE 2021-11-07 08:00:00
12 11/07/2021 02:30  0.124 A     2021-11-07 02:30:00 FALSE 2021-11-07 08:30:00
13 11/07/2021 03:00  0.074 A     2021-11-07 03:00:00 FALSE 2021-11-07 09:00:00
14 03/14/2021 01:30  0.02  B     2021-03-14 01:30:00 FALSE 2021-03-14 07:30:00

After the pivot I get:

library(tidyverse)
library(lubridate)

tz <- "America/Chicago"

df <- tribble(~Date, ~Time, ~Value, ~Type,
             "03/14/2021",	"01:30",	0.153, "A",
             "03/14/2021",	"01:45",	0.148, "A",
             "03/14/2021",	"02:00",	NA, "A",
             "03/14/2021",	"02:30",	NA, "A",
             "03/14/2021",	"03:00",	0.110, "A",
             "11/07/2021",	"00:30",	0.077, "A",
             "11/07/2021",	"01:00",	0.096, "A",
             "11/07/2021",	"01:30",	0.107, "A",
             "11/07/2021",	"02:00",	0.070, "A",
             "11/07/2021",	"01:30",	0.125, "A",
             "11/07/2021",	"02:00",	0.071, "A",
             "11/07/2021",	"02:30",	0.124, "A",
             "11/07/2021",	"03:00",	0.074, "A",
             "03/14/2021",	"01:30",	0.020, "B",
             "03/14/2021",	"02:00",	NA, "B",
             "03/14/2021",	"02:30",	NA, "B",
             "03/14/2021",	"03:00",	0.020, "B",
             "11/07/2021",	"00:30",	0.020, "B",
             "11/07/2021",	"01:00",	0.020, "B",
             "11/07/2021",	"01:30",	0.020, "B",
             "11/07/2021",	"02:00",	0.020, "B",
             "11/07/2021",	"01:30",	0.020, "B",
             "11/07/2021",	"02:00",	0.020, "B",
             "11/07/2021",	"02:30",	0.020, "B",
             "11/07/2021",	"03:00",	0.020, "B"
)

Test_data <- df %>% 
  mutate(DateTime=lubridate::mdy_hm(paste0(Date," ", Time), tz=tz)) %>% 
  mutate(DST=dst(DateTime)) %>% 
  mutate(NewDate=with_tz(DateTime, "UTC")) %>% 
  mutate(NewDate=force_tz(NewDate, "UTC"))  # %>%
 
  pivot_wider(NewDate, names_from="Type", values_from="Value")
  

[Ajackson]

Well I'm closer to an answer...

The only problem here is that 1 AM on the Fall transition day needs to be considered part of DST, which I suspect it is not, officially, but my data source thinks it is...

MyNewDates <- MyDates %>% 
  mutate(LDate=mdy_hm(Date, tz=tz)) %>% 
  mutate(DST=dst(LDate)) %>% 
  mutate(UDate=mdy_hm(Date, tz="UTC")) %>% # work in UTC to simplify
  group_by(date(LDate)) %>% # this takes care of Fall transition day, almost
     mutate(ToShift=duplicated(UDate, fromLast=TRUE)) %>% 
  ungroup() %>% 
  filter(!is.na(DST)) %>% # make room to shift times
  mutate(NewDate=if_else(DST|ToShift, UDate-hours(1), UDate))    

[Ajackson]

Got it.

MyNewDates <- MyDates %>% 
  mutate(LDate=mdy_hm(Date, tz=tz)) %>% 
  mutate(DST=dst(LDate)) %>% 
  mutate(DST=dst(LDate) | dst(LDate-1)) %>% # subtract 1 second to push time into DST
  mutate(UDate=mdy_hm(Date, tz="UTC")) %>% 
  group_by(date(LDate)) %>% # this takes care of Fall transition day, almost
     mutate(ToShift=duplicated(UDate, fromLast=TRUE)) %>% 
  ungroup() %>% 
  filter(!is.na(DST)) %>% 
  mutate(NewDate=if_else(DST|ToShift, UDate-hours(1), UDate))