|
|
1. First we need some test data to play with. Create it using stored procedure:
USE colombo;
-- this table will store test log records
CREATE TABLE mytestlog
(
id INT NULL,,
logdt DATETIME NOT NULL,
);
/* =====================================================
Author: amper
Create date: 2007-12-09
Description: Create test log table
======================================================= */
DROP PROCEDURE IF EXISTS sp_init_testlog;
CREATE PROCEDURE sp_init_testlog
(IN p_maxno INT, IN p_maxi INT)
BEGIN
DECLARE v_no, v_i INT;
DECLARE v_tmpstr VARCHAR(80);
TRUNCATE TABLE mytestlog;
SET v_no = 1;
SET v_tmpstr = 'qwertyuopasdfghzxcvbnmp0123456789';
WHILE (v_thedate < p_tdate) DO
-- do some work to waste time
SET v_i = 1;
WHILE (v_i < p_maxi) DO
SET v_tmpstr = REPLACE(v_tmpstr,'ab','wN');
SET v_tmpstr = REPLACE(v_tmpstr,'z','s');
SET v_tmpstr = REPLACE(v_tmpstr,'0','1');
SET v_i = v_i + 1;
END WHILE;
-- insert current_time into log table
INSERT INTO mytestlog (id, logdt)
VALUES (v_no, NOW());
-- move counter
SET v_no = v_no + 1;
END WHILE;
END;
|
Run stored procedure and see results:
CALL colombo.sp_init_testlog (5, 100000);
/* pay attention that on faster machine second argument, that defines time
delay have to be bigger */
SELECT * FROM colombo.mytestlog;
|
The results will be as follows:
|
id | logdt |
| 1 | 20071210 14:00:49 |
| 2 | 20071210 14:00:52 |
| 3 | 20071210 14:00:54 |
| 4 | 20071210 14:00:56 |
Now we can calculate time delay between every insert into the log table:
SELECT a.id AS prev_id, b.id AS curr_id,
TIMEDIFF(b.logdt,a.logdt) AS time_delta
FROM mytestlog AS a, mytestlog AS b
WHERE a.id = (b.id - 1)
|
The results will be as follows:
| prev_id | curr_id | time_delta |
| 1 | 2 | 00:00:03 |
| 2 | 3 | 00:00:02 |
| 3 | 4 | 00:00:02 |
2. Now I'd like to mention one Date Construction function
MAKEDATE(), that creates date value from given year and sequential
day number in this year:
/*
seq_date is a test dates squence table, and it was created
here
*/
SELECT sy,day_no,
DATE_FORMAT(MAKEDATE(sy,day_no),
GET_FORMAT(DATE,'ISO')) AS the_date
FROM seq_dates
WHERE day_no
BETWEEN 150 AND 153
|
The results will be as follows:
| sy | day_no | the_date |
| 1999 | 150 | 1999-05-30 |
| 1999 | 151 | 1999-05-31 |
| 1999 | 152 | 1999-06-01 |
| 1999 | 153 | 1999-06-02 |
|