如何在dbplyr中执行floor_date（）

时间：2020-11-27 09:45:44

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:

为了做到这一点，我想计算一个小时列，其中包含读数发生的日期和小时。然后我可以做一个简单的group_by总结。例如，我的tbl_df看起来像：

# 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.

我想要一个像这样的新专栏：2018-07-06 20：00：00.000或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.

通常我会使用来自lubridate的floor_date（时间，“小时”），甚至str_replace（时间，“。{2}（？=：[^：] * $）”，“00”），但两者都不适合我与我的SQL Server连接。

Any idea how this is done in R? Answer must R code and preferrably be dplyr code such as:

知道如何在R中完成这项工作吗？答案必须是R代码，最好是dplyr代码，例如：

# 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))

1 个解决方案

#1

0  

WORKS BUT NEEDS IMPROVEMENT

my_table %>%
      mutate(hour = "hour",
             time_hour = DATEADD(hour, DATEDIFF(hour, 0, time), 0)) %>%
      select(-hour)

#1

0  

WORKS BUT NEEDS IMPROVEMENT

my_table %>%
      mutate(hour = "hour",
             time_hour = DATEADD(hour, DATEDIFF(hour, 0, time), 0)) %>%
      select(-hour)
标签：

相关文章