How to Group Time by Hour or by 10 Minutes

How to group time by hour or by 10 minutes

finally done with

GROUP BY
DATEPART(YEAR, DT.[Date]),
DATEPART(MONTH, DT.[Date]),
DATEPART(DAY, DT.[Date]),
DATEPART(HOUR, DT.[Date]),
(DATEPART(MINUTE, DT.[Date]) / 10)

Grouping a datetime by a specific amount of minutes in SQL/Impala

You can use arithmetic on minutes:

SELECT MINUTES_ADD(HOURS_ADD(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(date_part AS STRING), "yyyyMMdd"))), hour_part), FLOOR(minute_part / 15)) AS date_time,
    AVG(kpi)
FROM table
GROUP BY date_part, hour_part,
         FLOOR(minute_part / 15)

Grouping pandas DataFrame by 10 minute intervals

df['timestamp'] = pd.to_datetime(df['timestamp'])
diffs = df['timestamp'] - df['timestamp'].shift()
laps = diffs > pd.Timedelta('10 min')
periods = laps.cumsum().apply(lambda x: 'period_{}'.format(x+1))
df['10min_period'] = periods

Pandas group by time interval (5min, 10min, 1day, 1year) and count amount of entries

Are you looking for something like this, for minute intervals:

df.groupby(['point',df.timestamp_local.dt.floor('5Min')]).size()

and this, for month/year

df.groupby(['point', df.timestamp_local.dt.to_period('M')]).size()

Mapping ymd_hms time into 15 minute time intervals

Please find below a solution using data.table and lubridate

Reprex

• Code

library(data.table)
library(lubridate)

DT[, date_15min_grp := fcase(minute(date) < 15, 1,
                             minute(date) < 30, 2,
                             minute(date) < 45, 3,
                             default = 4)][]

• Output

#>                   date date_15min_grp
#> 1: 2019-01-01 00:03:04              1
#> 2: 2019-01-01 00:07:03              1
#> 3: 2019-01-01 00:15:23              2
#> 4: 2019-01-01 00:16:28              2
#> 5: 2019-01-01 00:21:30              2

^{Created on 2021-11-30 by the reprex package (v2.0.1)}

---

AS A FOLLOW-UP TO YOUR COMMENT

• Code

library(data.table)
library(lubridate)

DT[, date_15min_grp := fcase(minute(date) < 15, hour(date)*4 + 1,
                             minute(date) < 30, hour(date)*4 + 2,
                             minute(date) < 45, hour(date)*4 + 3,
                             minute(date) < 60, hour(date)*4 + 4)][]

• Output

#>                   date date_15min_grp
#> 1: 2019-01-01 00:03:04              1
#> 2: 2019-01-01 00:07:03              1
#> 3: 2019-01-01 00:15:23              2
#> 4: 2019-01-01 00:16:28              2
#> 5: 2019-01-01 00:21:30              2

^{Created on 2021-12-01 by the reprex package (v2.0.1)}

Aggregating data at 15 minutes interval based on date and hour in R

Not exactly giving your expected results though, but perhaps usable.
You can see if this fits your needs.

library(data.table)
setDT(df)

df[, Time := ymd_hm(Time)]
df[, groups := lubridate::round_date(Time, "15 minutes")]
df[, .(Distance_m_sum = sum(Distance_m)), by = groups]

               groups Distance_m_sum
1: 2021-08-30 07:30:00            324
2: 2021-08-30 08:00:00            162
3: 2021-08-30 08:15:00            324
4: 2021-08-30 08:30:00            162
5: 2021-08-31 02:45:00            469
6: 2021-08-31 03:00:00            137
7: 2021-08-31 07:45:00            302
8: 2021-08-31 06:00:00             42

More extended example

You have to define your quarters I think, there are with the lubridate approach three options, round_date, floor_date and ceiling_date. Rethinking my own example I would pick floor_date as 2021-08-30 7:24 falls in the 7:15-7:30 group. To see all variants:

library(data.table)
setDT(df)

df[, Time := ymd_hm(Time)]
df[, round_date := lubridate::round_date(Time, "15 minutes")]
df[, floor_date := lubridate::floor_date(Time, "15 minutes")]
df[, ceiling_date := lubridate::ceiling_date(Time, "15 minutes")]

df[, .(Distance_m_sum = sum(Distance_m)), by = round_date]
            round_date Distance_m_sum
1: 2021-08-30 07:30:00            324
2: 2021-08-30 08:00:00            162
3: 2021-08-30 08:15:00            324
4: 2021-08-30 08:30:00            162
5: 2021-08-31 02:45:00            469
6: 2021-08-31 03:00:00            137
7: 2021-08-31 07:45:00            302
8: 2021-08-31 06:00:00             42

df[, .(Distance_m_sum = sum(Distance_m)), by = floor_date]
        floor_date Distance_m_sum
1: 2021-08-30 07:15:00            162
2: 2021-08-30 07:30:00            162
3: 2021-08-30 07:45:00            162
4: 2021-08-30 08:15:00            486
5: 2021-08-31 02:30:00            319
6: 2021-08-31 02:45:00            287
7: 2021-08-31 07:30:00            122
8: 2021-08-31 07:45:00            180
9: 2021-08-31 06:00:00             42

df[, .(Distance_m_sum = sum(Distance_m)), by = ceiling_date]
          ceiling_date Distance_m_sum
1: 2021-08-30 07:30:00            324
2: 2021-08-30 08:00:00            162
3: 2021-08-30 08:30:00            486
4: 2021-08-31 02:45:00            319
5: 2021-08-31 03:00:00            287
6: 2021-08-31 07:45:00            224
7: 2021-08-31 08:00:00             78
8: 2021-08-31 06:15:00             42

---

Related Topics