The oracle technology-CodePudding

Three parameters start time and end time, interval
Demand is: in the start time and end time, carried out in accordance with the specified time interval a grouping, such as the start time for the 2020-07-10 08:00:00
End time for the 2020-07-11 08:00:00 time interval for 30 minutes
Is divided into 2020-07-10 08:00:00-2020-07-10 08:30:00 so on until the end of the 2020-07-11 07:30:00-2020-07-11 08:00:00 how to write SQL statements, have who can help me

CodePudding user response:

Select ceil ((to_date (' 09:30:00 2020-07-11 ', '- dd yyyy - mm hh24: mi: ss') - start time) * 24 * 60/30)
The from tab_name
The where... ;

CodePudding user response:

reference 1st floor js14982 response:

select ceil ((to_date (' 09:30:00 2020-07-11 ', '- dd yyyy - mm hh24: mi: ss') - start time) * 24 * 60, 30)
The from tab_name
The where... ;

big brother not ah

CodePudding user response:

refer to the second floor js14982 response:

select ceil ((to_date (' 09:30:00 2020-07-11 ', '- dd yyyy - mm hh24: mi: ss') - start time) * 24 * 60/30)
The from tab_name
The where... ;

not to big brother

CodePudding user response:

references coffee without milk with sugar, 4/f response:

Quote: refer to the second floor js14982 response:
select ceil ((to_date (' 09:30:00 2020-07-11 ', '- dd yyyy - mm hh24: mi: ss') - start time) * 24 * 60/30)
The from tab_name
The where... ;

not to big brother

how don't come, what is wrong?

CodePudding user response:

Big brother, try the following method to normalize time group by

 - making field X type date, if the text is to_date 
Select the decode (sign (X-ray trunc (X, 'hh) - 30/1440), 1, trunc (X,' hh) + 30/1440), round (X, 'hh)) as time from the table where X & gt;=start time and X & lt;=end time

CodePudding user response:

Braces for per dozen, editor himself added a bracket

 - making field X type date, if the text is to_date 
Select the decode (sign (X-ray trunc (X, 'hh) - 30/1440), 1, trunc (X,' hh) + 30/1440, round (X, 'hh)) from the as time table where X & gt;=start time and X & lt;=end time

CodePudding user response:

 
With tab1 as (
The select level g_id to_date (' 2020071108 ', 'yyyymmddhh24) + (level 1)/2 * 1/24 dt1, to_date (' 2020071108', 'yyyymmddhh24) + (level)/2 * 1/24 dt2 from dual connect by level & lt;=floor ((to_date (' 2020071108 ', 'yyyymmddhh24) - to_date (' 2020071008', 'yyyymmddhh24))/(86400/(30 * 60))) + 2 
) 
Select * from the TAB t1, tab1 t2 
Where t1. _date between t2. Dt1 and t2. The dt2