stevensjn@gmail.com wrote in news:1170432275.685818.65760
@p10g2000cwp.googlegroups.com:

> I was wondering if anyone knew of a way to fill a table with rows of
> sequential hours spanning a number of years using a query or set of
> queries.

USE test;

DROP TABLE IF EXISTS tbluhahours;
CREATE TABLE tbluhahours (
StartDate datetime NOT NULL,
EndDate datetime NOT NULL,
PRIMARY KEY (`StartDate`,`EndDate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE _helper (i TINYINT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO _helper (i) VALUES
(0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SET @counter := -1;
SET @start := '2000-01-01 00:00:00';
SET @finish := '2010-12-31 23:59:00';
INSERT INTO tbluhahours (StartDate, EndDate)
SELECT
DATE(@start) + INTERVAL H.c HOUR,
DATE(@start) + INTERVAL H.c + 1 HOUR - INTERVAL 1 SECOND

FROM (
SELECT @counter := @counter + 1 AS c
FROM _helper h1 -- 10
CROSS JOIN _helper h2 -- 100
CROSS JOIN _helper h3 -- 1000
CROSS JOIN _helper h4 -- 10000
CROSS JOIN _helper h5 -- 100000
) H

WHERE
@start + INTERVAL H.c HOUR < @finish;



--
felix