Columns with date

[Tore Voldbekk]

I have made a MySql database that records values every day from some
experiments. The database have x columns across. One with the name of
the experiment and the rest with the values. For example:

Experiment z21092004 z22092004 z23092004 z24092004 ++
dlk322 2 2 3 7
der42441 5 53 3 5

and so forth.

The z21092004 derives from 21 September 2004, with values going back a
few months. Now I need the data from the database and have created a
html/Python solution where one type in the experiment and gets out the
value of the first day. However, I would like to get out the values
from a range of dates. For example, select August 4 and September 2,
and then get out the sum of the values from that period of time.
However, I would not like to type "select sum from z21092004 and
z22092004 and ...." That would be hopeless especially since one might
want out the values from the span of for example 6 months. This seems
very hard when the database is organised as it is. Is there some way to
for example create a second name for each column? So that z21092004
also responded to eg 1, z22092004 to 2 and so forth. And then some way
to get the sum of column 45-68 or so forth? Or is there some better way
to organize the data?

Thanks in advance

[Bill Karwin]

Tore Voldbekk wrote:

I have made a MySql database that records values every day from some
experiments. The database have x columns across. One with the name of
the experiment and the rest with the values. ... is there some better way to organize the data?

Yes, I would create a second table with rows corresponding to the daily
values, instead of columns. For instance:

create table experiment (
experiment_id integer not null auto_increment,
experiment_name varchar(20) not null
);

and a daily value table:

create table experiment_value (
experiment_id integer not null,
experiment_date date not null,
experiment_value integer not null
);

Then you can easily get a sum of a range of dates like so:

select x.experiment_name, sum(v.experiment_value)
from experiment x inner join experiment_value v
on x.experiment_id = v.experiment_id
where v.experiment_date between '2004-08-04' and '2004-09-02';

Regards,
Bill K.