List:General Discussion« Previous MessageNext Message »
From:Harald Fuchs Date:November 7 2003 3:23pm
Subject:Re: How to generate data sequences simple way?
View as plain text  
In article <3FAB5E87.7050506@stripped>,
Petr <mat127@stripped> writes:

> Hello,
> I hope someone can help me with the following problem: I have a table
> containing integer counters associated with particular datetime like
> this:


> 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
  FROM seq
  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.

Thread
How to generate data sequences simple way?Petr7 Nov
  • Re: How to generate data sequences simple way?Martijn Tonies7 Nov
  • Re: How to generate data sequences simple way?Harald Fuchs7 Nov