I'm trying to aggregate minute-level time series data to hourly level via averaging.
In order to do that I want to calculate an hour column that has the day and hour that the reading occurred in. Then I can do a simple group_by summarise. For instance, my tbl_df looks like:
# Database: Microsoft SQL Server 13.00.4001[<SERVER>/<Project>]
eGauge time Channel End_Use Metric Circuit Reading mean_lag
<int> <chr> <chr> <chr> <chr> <chr> <dbl> <dbl>
1 30739 2018-07-06 20:04:00.000 8.0 Clothes Washer P <NA> 0.000033333 60
2 30739 2018-07-06 20:13:00.000 3.0 Clothes Dryer P <NA> 0.000833333 60
3 30739 2018-07-06 21:16:00.000 6.0 Cooktop P <NA> 0.000050000 60
4 30739 2018-07-06 21:00:00.000 3.0 Clothes Dryer P <NA> 0.000833333 60
5 30739 2018-07-06 21:46:00.000 8.0 Clothes Washer P <NA> 0.000016667 60
6 30739 2018-07-07 02:06:00.000 3.0 Clothes Dryer P <NA> 0.001016667 1
7 30739 2018-07-07 08:52:00.000 1.0 Service Mains P <NA> 1.814516667 1
8 30739 2018-07-07 08:52:00.000 3.0 Clothes Dryer P <NA> 0.001050000 1
9 30739 2018-07-07 08:52:00.000 4.0 Central AC P <NA> 0.043000000 1
10 30739 2018-07-07 08:52:00.000 5.0 Oven P <NA> 0.021333333 1
and I would like a new column like this: 2018-07-06 20:00:00.000 or 2018-07-06 20:00:00.000.
Normally I would use floor_date(time, "hour") from lubridate, or even str_replace(time, ".{2}(?=:[^:]*$)", "00"), but neither are working for me with my SQL Server connection.
Any idea how this is done in R? Answer must R code and preferrably be dplyr code such as:
# NOT WORKING
my_table %>%
mutate(time_hour = floor_date(time, "hour"))
or
# NOT WORKING
my_table %>%
mutate(time_hour = DATEADD('hour', DATEDIFF('hour', 0, time), 0))
my_table %>%
mutate(time_hour = DATEADD(sql("hour"), DATEDIFF(sql("hour"), 0, time), 0))