List:General Discussion« Previous MessageNext Message »
From:Petr Date:November 7 2003 8:57am
Subject:How to generate data sequences simple way?
View as plain text  
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.

Thanks, Petr

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