Have Geek Brain

How to select last 24 hours of rows from row offset

Im trying to calculate the root mean square value (root mean square value) from a dataset stored in MySQL. I need to capture the last 24 hours of points that occur over a 24 hour period before a certain line offset. For example, if I wanted to compute the 24 hour rms for a string id 1250 that has a timestamp of June 7, 2007 at midnight, I would need to get all points that occur between it and June 6, 2007 at midnight.

+1


a source to share


2 answers


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 need to adjust the date and time type to match your data.

+3


a source


You can use 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'
+2


a source






More articles:


All Articles