title: How to loop the datetime in mysql stored procedure
body: <p>im using mysql stored procedure, how can I make a loop that in every 1 hour the variable strathour will have 1 hour interval then return the ouptut of the query which is total. While the starthour is less than the date of november it will have interval of 1 hour and perform the query.</p>
  
  <p>here's my code:</p>
  
  <pre><code>DELIMITER $$
  
  CREATE DEFINER=`root`@`%` PROCEDURE `sp_asessiontime`(
  out `total` int
  )
  BEGIN
  
  declare `starthour`, `endhour` datetime;
  set `starthour` = '2012-09-20 01:59:00';
  set `endhour` = '2012-09-20 02:00:00';
  
  
  select count(terminalcount.terminalids) into total from (
  select distinct ts.TerminalID `terminalids` from
  tmptransactiondetails td
  inner join transactionsummary ts
  on td.TransactionSummaryID = ts.TransactionsSummaryID
  where
  td.ServiceID = 4
  and
  td.TransactionType in ('D','W')
  and
  (ts.DateStarted &gt;= starthour and ts.DateStarted &lt; endhour)
  or
  (ts.DateEnded &gt;= starthour and ts.DateEnded &lt; endhour)
  or
  (ts.DateStarted &lt; starthour and starthour &lt;= ts.DateEnded)
  )as terminalcount;
  </code></pre>
  
  <p>-- loop for every 1 hour</p>
  
  <pre><code>while 
  
  starthour &lt; '2012-11-01 01:59:00' do
  select starthour + interval 1 hour;
  select total as totalnumber;
  
  end while;
  
  END
  </code></pre>
  
  <p>Thank you so much guys.</p>