There is MySQL in the tag, so I will upload the MySQL version.
If you approach it with IF, there will be no answer because this problem can only be solved by finding a rule that can be applied consistently for all data.
-- Assume that the tbl1 table has an id, created_at column.
SELECT
concat(
s.d, ':00~', -- 4. For example, if s.d === 2022-07-22 12:00?
DATE_FORMAT (DATE_ADD (concat(s.d, ':00:00'), INTERVAL 1 HOUR), '%H:00') -- 5. You need to obtain a date that adds 1 hour to it, convert it to the form "13:00", and paste it after s.d.
) AS 'Wanted Outcome',
s.c AS 'count'
FROM (
SELECT
DATE_FORMAT(t.created_at, '%Y-%m-%d%H') ASd, --3. Select values grouped for output and further formatting.
count (t.id) ASc -- 2. Aggregates.
FROM tbl1 t
GROUP BY DATE_FORMAT (t.created_at, '%Y-%m-%d %H') --1. Group as of the start of the year-month-day.
) s;
>>> import pandas as pd
>>> from io import StringIO
>>> dat = ''''Time
2021-01-31 21:45:03
2021-01-31 20:41:27
2021-01-31 20:20:55
2021-01-31 20:01:59
2021-01-31 19:45:03
2021-01-31 19:12:49
2021-01-31 18:40:24
2021-01-31 18:22:34'''
>>> df = pd.read_csv(StringIO(dat))
>>> df
Time
0 2021-01-31 21:45:03
1 2021-01-31 20:41:27
2 2021-01-31 20:20:55
3 2021-01-31 20:01:59
4 2021-01-31 19:45:03
5 2021-01-31 19:12:49
6 2021-01-31 18:40:24
7 2021-01-31 18:22:34
>>> df.resample ("H", on="Time").count()
Traceback (most recent call last):
File "<pyshell#22>", line 1, in <module>
df.resample ("H", on="time").count()
File "C:\PROGRAMS\Python3864\lib\site-packages\pandas\core\frame.py", line 10350, in resample
return super().resample(
File "C:\PROGRAMS\Python3864\lib\site-packages\pandas\core\generic.py", line 8126, in resample
return get_resampler(
File "C:\PROGRAMS\Python3864\lib\site-packages\pandas\core\resample.py", line 1382, in get_resampler
return tg._get_resampler(obj, kind=kind)
File "C:\PROGRAMS\Python3864\lib\site-packages\pandas\core\resample.py", line 1558, in _get_resampler
raise TypeError(
TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Index'
>>> df["Time"] = pd.to_datetime (df["Time"])
>>> df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 8 entries, 0 to 7
Data columns (total 1 columns):
# # Column Non-Null Count Dtype
--- ------ -------------- -----
0 hours 8 non-null datetime64[ns]
dtypes: datetime64[ns](1)
memory usage: 192.0 bytes
>>> df.resample ("H", on="Time").count()
Time
Time
2021-01-31 18:00:00 2
2021-01-31 19:00:00 2
2021-01-31 20:00:00 3
2021-01-31 21:00:00 1
© 2024 OneMinuteCode. All rights reserved.