My reproducible example below is based in part on this example of changing time zones in R.
I am struggling to extract the correct (EST) date and time out of a POSIXct column that is in UTC time zone. Please see my quick example on this:
> dput(mydf)
structure(list(scheduled = structure(c(1541525400, 1541538000,
1541545200, 1541545500, 1541547000, 1541547000, 1541547000, 1541547000,
1541547000, 1541548800, 1541548800, 1541548800, 1541548800, 1541548800,
1541548800, 1541548800, 1541548800, 1541548800, 1541548800, 1541548800
), class = c("POSIXct", "POSIXt"), tzone = "UTC"), homePoints = c(110L,
82L, 77L, 102L, 76L, 104L, 89L, 84L, 77L, 97L, 71L, 114L, 91L,
105L, 74L, 100L, 74L, 102L, 68L, 89L), awayPoints = c(54L, 67L,
67L, 82L, 55L, 55L, 49L, 70L, 67L, 63L, 72L, 39L, 52L, 55L, 77L,
80L, 66L, 48L, 75L, 76L)), row.names = c(NA, 20L), class = "data.frame")
> mydf
scheduled homePoints awayPoints
1 2018-11-06 17:30:00 110 54
2 2018-11-06 21:00:00 82 67
3 2018-11-06 23:00:00 77 67
4 2018-11-06 23:05:00 102 82
5 2018-11-06 23:30:00 76 55
6 2018-11-06 23:30:00 104 55
7 2018-11-06 23:30:00 89 49
8 2018-11-06 23:30:00 84 70
9 2018-11-06 23:30:00 77 67
10 2018-11-07 00:00:00 97 63
11 2018-11-07 00:00:00 71 72
12 2018-11-07 00:00:00 114 39
13 2018-11-07 00:00:00 91 52
14 2018-11-07 00:00:00 105 55
15 2018-11-07 00:00:00 74 77
16 2018-11-07 00:00:00 100 80
17 2018-11-07 00:00:00 74 66
18 2018-11-07 00:00:00 102 48
19 2018-11-07 00:00:00 68 75
20 2018-11-07 00:00:00 89 76
Things go wrong when I attempt to (1st) change the time zone of the scheduled column, and then try to create separate gameDate and gameTime columns:
> attr(mydf$scheduled, "tzone")
[1] "UTC"
> attr(mydf$scheduled, "tzone") <- "EST"
> attr(mydf$scheduled, "tzone")
[1] "EST"
> head(mydf)
scheduled homePoints awayPoints
1 2018-11-06 12:30:00 110 54
2 2018-11-06 16:00:00 82 67
3 2018-11-06 18:00:00 77 67
4 2018-11-06 18:05:00 102 82
5 2018-11-06 18:30:00 76 55
6 2018-11-06 18:30:00 104 55
mydf <- mydf %>%
dplyr::mutate(gameDate = as.Date(scheduled)) %>%
dplyr::mutate(gameTime = strftime(scheduled, format="%H:%M:%S"))
> head(mydf, 12)
scheduled homePoints awayPoints gameDate gameTime
1 2018-11-06 12:30:00 110 54 2018-11-06 17:30:00
2 2018-11-06 16:00:00 82 67 2018-11-06 21:00:00
3 2018-11-06 18:00:00 77 67 2018-11-06 23:00:00
4 2018-11-06 18:05:00 102 82 2018-11-06 23:05:00
5 2018-11-06 18:30:00 76 55 2018-11-06 23:30:00
6 2018-11-06 18:30:00 104 55 2018-11-06 23:30:00
7 2018-11-06 18:30:00 89 49 2018-11-06 23:30:00
8 2018-11-06 18:30:00 84 70 2018-11-06 23:30:00
9 2018-11-06 18:30:00 77 67 2018-11-06 23:30:00
10 2018-11-06 19:00:00 97 63 2018-11-07 00:00:00
11 2018-11-06 19:00:00 71 72 2018-11-07 00:00:00
12 2018-11-06 19:00:00 114 39 2018-11-07 00:00:00
... uh oh, these dates and times aren't right (all times wrong, rows 10-20 for date also wrong).
Unfortunately, I cannot use the same approach of using (attr) to change these new columns:
> attr(mydf$gameDate, "tzone") <- "EST"
> attr(mydf$gameTime, "tzone") <- "EST"
> head(mydf)
scheduled homePoints awayPoints gameDate gameTime
1 2018-11-06 12:30:00 110 54 2018-11-06 17:30:00
2 2018-11-06 16:00:00 82 67 2018-11-06 21:00:00
3 2018-11-06 18:00:00 77 67 2018-11-06 23:00:00
4 2018-11-06 18:05:00 102 82 2018-11-06 23:05:00
5 2018-11-06 18:30:00 76 55 2018-11-06 23:30:00
6 2018-11-06 18:30:00 104 55 2018-11-06 23:30:00
... this didnt help.
Any thoughts / help on how I can get the correct (EST) date and times out of this POSIXct column would be greatly appreciated! Thanks in advance!
