Complete instance of Oracle date grouping query

Time:2022-5-14

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!

Recommended Today

Analysis of Django interface version control

catalogue 1、 Foreword 2、 Disposition 2.1. Global configuration 2.2. View configuration 3、 Five version control classes built in DRF 3.1、AcceptHeaderVersioning 3.1.1. HTTP access method 3.1.2、settings 3.1.3、serializers 3.1.4、views 3.1.5 access 3.2、URLPathVersioning 3.2.1. HTTP access method 3.2.2、settings 3.2.3、urls 3.2.4 access 3.3、NamespaceVersioning 3.3.1. HTTP access method 3.3.2、settings 3.3.3、urls 3.3.4 access 1、 Foreword stayRESTfulIn the specification, for problems related […]