MySQL time manipulation functions and stored procedures

Because the business needs to count a batch of data, using the time operation function and stored procedure about MySQL, the problem has been basically solved, the process is recorded:

1. MySQL statement does not support the direct use of loops, the loop can only be used in the stored procedure;

2. When writing as a file, note some hidden characters, resulting in grammatical errors. The comments in this example contain some invisible characters that are not found.

3. In the stored procedure as far as possible use cent good, splits the open statement. In this case, the drop sentence was initially not written, leading to a mistake.

4. Time function is very powerful, can avoid some work. Http://www.cnblogs.com/ggjucheng/p/3352280.html

5. Optimization of SQL statements is important, in this case only the problem is resolved, but the amount of data is too large for the stored procedure to run for a long time. Expect someone to help optimize optimizations in the while statement.

The following code is posted:

--* the data in the table test.transport20140901 table at intervals of five minutes, the number of traffic flows at each intersection is r--* @start_time The starting time is five minute intervals of the hourly time such as 2014-09-01 00:20:00--* @end _time termination time is five minute interval of the hour and greater than start_time such as 2014-09-01 01:00:00--* The statistical range contains the start time, but does not contain the end time delimiter $drop procedure Transport_statu        S;create procedure Transport_status (start_time datetime,end_time datetime) BEGIN declare mid_start_time datetime;        DECLARE mid_end_time datetime;        Set Mid_start_time=start_time;        Set Mid_end_time=date_add (start_time, interval 5 minute); Lab:while Mid_start_time < end_time do insert into Test.transport_status (stamp,s  Tamp_time,address,car_count) (Select Floor (Unix_timestamp (time)/300)                                As Stmp, Date_format (Mid_end_time, '%y-%m-%d%h:%i:%s ') as TM, Address, count (address) as CNT from TEST.T Ransport20140901 where time > Date_add (mid_start_time, interval-1 second)                and Time < Mid_end_time Group by address);                Set Mid_start_time=date_add (Mid_start_time, interval 5 minute);        Set Mid_end_time=date_add (Mid_end_time, interval 5 minute); End While Lab;end $delimiter; call Transport_status ("2014-09-01 00:00:00", "2014-09-2 00:00:00");

MySQL time manipulation functions and stored procedures