PHP/MySQL ->
Date and Time manipulation in MySQL

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:
idlogdt
120071210 14:00:49
220071210 14:00:52
320071210 14:00:54
420071210 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_idcurr_idtime_delta
1200:00:03
2300:00:02
3400: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:
syday_nothe_date
19991501999-05-30
19991511999-05-31
19991521999-06-01
19991531999-06-02



sqlexamples.info