unexpected result using aggregate on date

i have a date_time POSIXct object in a large (6 month) dataframe in 5 second increments that i want to aggregate into 30s 'blocks'. 6x 5s is 30s so nrow(df)/6 gives the correct sequence length.

I tried the following:

Date_time_30s <- aggregate(Mn$Date_time, list(seq(0, length.out = nrow(Mn)) %/% 6), FUN = mean)

The first 6 date_times look like this:

"","Date_time","Depth","Temperature","Light_Level","Date"
"1",2013-10-14 12:30:00, 
"2",2013-10-14 12:30:05,
"3",2013-10-14 12:30:10,
"4",2013-10-14 12:30:15,
"5",2013-10-14 12:30:20,
"6",2013-10-14 12:30:25,

and so the mean should be 2013-10-14 12:30:12.5 but it comes out as 2013-10-14 11:30:12. no decimal second (a simple formatting issue solved by options(digits.secs=3) ) but the hour is wrong.

What's going wrong?

dput(head(Mn))

structure(list(Date_time = structure(c(1381721400, 1381721405, 
1381721410, 1381721415, 1381721420, 1381721425), class = c("POSIXct", 
"POSIXt"), tzone = "Asia/Tokyo"), Depth = c(64.4476273148148, 
65.9476334145628, 65.9476395143109, 66.4476456140589, 67.9476517138069, 
66.9476578135549), Temperature = c(27.549999, 27.5, 27.400002, 
27.35, 27.25, 27.200001), Light_Level = c(148L, 148L, 148L, 148L, 
147L, 147L), Date = structure(c(15992, 15992, 15992, 15992, 15992, 
15992), class = "Date"), vv = c(0, 0.300001, 1e-06, 0.100001, 
0.300001, -0.199999), vv_abs = c(0, 0.300001, 1e-06, 0.100001, 
0.300001, 0.199999)), row.names = c(NA, 6L), class = "data.frame")

CodePudding user response：

Run this before the code:

options(digits.secs=3)

Can you run this command? It will give you the result in different time zones. Tokyo should be the correct time.

library(lubridate)
library(dplyr)
Date_time_30s <- aggregate(Mn$Date_time, list(seq(0, length.out = nrow(Mn)) %/% 6), FUN = mean) %>% 
  mutate(Tokyo = with_tz(x, tzone = "Asia/Tokyo"),
         GMT = with_tz(x, tzone = "GMT"))