Bit of help with MySQL date and time functions

[The Excession]

I have a table with records that are time stamped - i.e. the MySQL timestamp column format.

What I want to do is calculate the number of minutes each record is past an arbitury starting timestamp and store that minute alue in another column so I can group records into 1 minute intervals.

Is there an easy way to do this post processing in SQL or am I better just hacking a PhP script to do it?

So, consider I have a contiguous set of records with IDs from 1 to 900, I want reocrds in the block from 1 to 75 to be measured against the timestamp of record 1, then I want records 76 to 300 to be measured against the timestamp for record 76. (I have other mechanisms for selecting the records - the ID column if just an example).

Record sets do cross hourly boundaries but are highley unlikely to ever roll across to another day.

Thanks for any pointers.

[ewenm]

In PL/SQL the INTERVAL functionality allows timestamp arithmetic. No idea if it's the same in MySQL.

[The Excession]

Thanks,

I've found a timediff function,

I guess my question is how do I know hold the value of the first record and them update all the others with the difference.

Is it going to require a stored procedure or can it be done in a simple bit of SQL, perhaps using a join where the pointer to the first record is fixed for the rest of the query?

[ewenm]

Comparing each record to a single master record is relatively easy - all of them compared to rec 1 for example. The difficult bit is how you tell it to switch to record 76 for 76-300 and so on.

[The Excession]

ewenm said:

Comparing each record to a single master record is relatively easy - all of them compared to rec 1 for example. The difficult bit is how you tell it to switch to record 76 for 76-300 and so on.

Don't worry about that, I can manage that already.

My head won't work this morning....

I just need a table alias/join that will hold the first id, and let me process all the further records according to the first. I only have a few blocks of records to process, so can enter the ids manually.

[PetrolTed]

Depending on the range of times in the table, you could group by minute.

SELECT * FROM table GROUP BY MINUTE( tmStamp );

... GROUP BY HOUR(tmStamp),MINUTE(tmStamp);

etc

http://dev.mysql.com/doc/refman/4.1/en/date-and-ti...

Edited by PetrolTed on Monday 1st March 12:19

[The Excession]

think I got it

select fixed.timestamp as t1,
 records.timestamp as t2,
 TIMESTAMPDIFF(MINUTE,fixed.timestamp,records.timestamp) AS minutes
from events fixed, events records

where fixed.id = 1268
AND records.id between 1268 and 1300

Edited by The Excession on Monday 1st March 12:27

[The Excession]

PetrolTed said:

Depending on the range of times in the table, you could group by minute.

SELECT * FROM table GROUP BY MINUTE( tmStamp );

... GROUP BY HOUR(tmStamp),MINUTE(tmStamp);

etc

http://dev.mysql.com/doc/refman/4.1/en/date-and-ti...

Edited by PetrolTed on Monday 1st March 12:19

Thanks Ted, I'll take a play with that too.