In article <3FAB5E87.7050506@stripped>,
Petr <mat127@stripped> writes:
> I hope someone can help me with the following problem: I have a table
> containing integer counters associated with particular datetime like
> CREATE TABLE `counters` (
> `when` datetime NOT NULL default '0000-00-00 00:00:00',
> `counter` smallint(5) unsigned NOT NULL default '0',
> For some purposes I need to initialize frequently the table for time
> sequences with constant time step e.g. since 2003-11-06 8:00 to
> 2003-11-06 12:00 with time step 15 minutes. It means I need to do
> following INSERT commands:
> INSERT INTO couters (..., '2003-11-06 8:00', 0, ...);
> INSERT INTO couters (..., '2003-11-06 8:15', 0, ...);
> INSERT INTO couters (..., '2003-11-06 8:30', 0, ...);
> INSERT INTO couters (..., '2003-11-06 12:00', 0, ...);
> Of course my C code is able to generate such command strings in one
> simple loop and send them to the MySQL server. But the code should be
> as fast as possible and I prefer to do this task by one INSERT-SELECT
> command like this:
> INSERT INTO counters SELECT ...
> Unfortunately I do not have any idea how to build such SELECT command
> that will generate the datetime sequence for me. Does anybody know how
> to do it? I would appreciate any hints very much.
You need to create a helper table containing just different numbers, e.g.
CREATE TABLE seq (val INT UNSIGNED PRIMARY KEY);
INSERT INTO seq VALUES (0), (1), ...
Then you can do
INSERT INTO counters (`when`, counter)
SELECT '2003-11-06 08:00:00' + INTERVAL val * 15 MINUTE, 0
WHERE val BETWEEN 0 AND 16;
BTW: I'd not use "when" as a column name. You always have to enclose it in
backticks because it conflicts with an SQL keyword.