I am very new to mysql scripts , I want to execute this query by incrementing 00:00:00 time to 30 minutes .
something like this
Select count(*)
FROM ctrdb.CTR_LINE_ITEM
where LOAD_DATE BETWEEN '2016-05-18 00:00:00' AND '2016-05-18 00:30:00'
order by load_date;
Select count(*)
FROM ctrdb.CTR_LINE_ITEM
where LOAD_DATE BETWEEN '2016-05-18 00:30:00' AND '2016-05-18 00:60:00'
order by load_date;
Can you guys please help me ?
if you wan to achieve this in mysql and want to get separate resultset for each query then you need to run your query in loop by using stored procedure.
read loop in mysql http://www.mysqltutorial.org/stored-procedures-loop.aspx
or there are not multiple queries then you can use union as well
DELIMITER $$
CREATE PROCEDURE getdata()
BEGIN
DECLARE x INT;
DECLARE maximum INT; # you can use date as well
DECLARE startdate DATE;
DECLARE enddate DATE;
SET x =0;
SET maximum = 10;
SET startdate = '2016-05-18 00:00:00';
loop_label: LOOP
IF x > 10 THEN
LEAVE loop_label;
END IF;
SET x = x + 1;
SET enddate = startdate + INTERVAL 30 MINUTE;
Select count(*) ,startdate
FROM ctrdb.CTR_LINE_ITEM
where LOAD_DATE BETWEEN '2016-05-18 00:00:00' AND '2016-05-18 00:30:00'
order by load_date;
SET startdate = startdate + INTERVAL 30 MINUTE;
END LOOP;
END $$
DELIMITER ;
