preface
When making a data report, you need to group a certain period of time and make statistics of various data at an interval of 1 hour, such as 9:00-10:00, 10:00-11:00… But now there is a situation that the time may not start at the whole point, and there will be half an hour. At 9:30, you need to group statistics according to 9:30-10:30 and 10:30-11:30
realization
Taking the starting time of the time range as the reference time, calculate the difference (minutes) / required time interval (1 hour)
Get the value as the basis for grouping.
example
select xx,
floor(
floor(
(refDate-to_date('2021-02-05 08:30','yyyy-MM-dd hh24:mi:ss'))
* 24 * 60) / 60
) factor
where refDate >=to_date('2021-02-05 08:30','yyyy-MM-dd hh24:mi:ss')
and refDate <=....
Floor: for rounding
Refdate: Date field in each data
2021-02-05 08:30: reference time
Factor: calculated grouping basis
The above query range is 21:30-23:30. Taking 21.30 as the reference time, the time period can be divided into 21.30-22:30 and 22:30-23:30..
On this basis, query the time period to which the time belongs
Above passfactor/24It means that the interval is one hour, which can be combined arbitrarily by changing these two values.
summary
This is the end of this article about Oracle date grouping query. For more information about Oracle date grouping query, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!