How to select the last 24 hours of rows from a row offset

Question

Im trying to compute a 24 hour rms (root mean squared) from a dataset stored in MySQL. I Need to grab the last 24 hours of points that occur within 24 hour period before a particular row offset. For instance if I wanted to compute 24hour rms for row id 1250 which has a timestamp of June 7th 2007 at midnight I would need to get all the points that happen between it and June 6th 2007 at midnight.

Answer 1

Off the top of my head... (MYSQL)

declare @endTime datetime;
select @endTime=timestamp from data where id=@rowId
select 
    * 
from 
    data 
where 
    timestamp<=@endtime and timestamp>ADDDATE(@endTime,INTERVAL -1 DAY)

(T-SQL)

declare @endTime datetime2;
select @endTime=timestamp from data where id=@rowId
select * from data where timestamp<=@endtime and timestamp>dateadd(d,-1,@endTime)

You may have to adjust the datetime type to match your data.

Answer 2

You may use the aggregate functions directly against some set:

select
  sqrt(sum(pow(my_value,2))/count(*))
from
  my_table
where
  my_date between '2007-06-06 00:00:00' and '2007-06-07 00:00:00'