Select rows from MySQL in the last 24 hours and skip rows based on interval X?

2
votes

I have created a trading bot and I use MySQL to import data and calculate technical indicators, I want to create a feature that allows me to import data more frequently and control the interval of how I select the data.

Is there a query that will allow me to select data at a fixed interval in mysql?

SELECT * FROM PriceHistory 
WHERE `RefrenceID`=1001
and `TimeStamp` > (SELECT max(`TimeStamp`) FROM PriceHistory) - 
Interval 1440 Minute
Group by `TimeStamp`;

Using this Query I am able to select price data for the Last 24 Hours. Is there a solution for me to select data in intervals of 5 minute, 10 minutes, 30 minutes etc?

DataSet Example

`TimeStamp`            `RefrenceID`

1. 2018-12-14 23:00:05   1001
2. 2018-12-14 23:05:10   1001
3. 2018-12-14 23:11:16   1001
4. 2018-12-14 23:16:21   1001
5. 2018-12-14 23:21:25   1001
6. 2018-12-14 23:26:30   1001
7. 2018-12-14 23:32:41   1001
8. 2018-12-14 23:37:46   1001
9. 2018-12-14 23:42:51   1001
10. 2018-12-14 23:47:51   1001
11. 2018-12-14 23:52:56  1001

I have thought of two possible solutions unfortunately I have yet figured out how to implement them.

  1. add an auto-increment-id to my table, create a query that selects the rownumber. create a local variable @rownum and select all rows where @rownum = @rownum + (interval).

  2. Select the first timestamp, create a local variables @start_time, @offset, @count then select min(TimeStamp)> @start_time + INTERVAL(@offset * @count)MINUTE

The issues I am facing by using an auto-increment ID solution is that I am tracking the price of 220 items in the same table (so sequential ids will not work) and therefore there may need to be a new index row created at the start of the query. The other issue I am facing is that my code is synchronous and therefore due to other running processes every import of data is between 5min - 5min 30sec.

thanks for your help!

best regards,

slurp

Expected output:

1. 2018-12-14 23:00:05   1001
3. 2018-12-14 23:11:16   1001
5. 2018-12-14 23:21:25   1001
7. 2018-12-14 23:32:41   1001
9. 2018-12-14 23:42:51   1001
11. 2018-12-14 23:52:56  1001
2
mysqlsql
To make it clear, what would the expected result be, based on your example data? – ADyson
I edited my question to fix timestamps and add expected output – SlurpGoose
This looks promising blog.timescale.com/… It's way more complex than your use case, but offers some ideas for how to solve some of the inherent problems – Will
Not sure why the 10. line is in the expected output. Its within the same 10 mins as 9. – danblack
edited question to reflect error – SlurpGoose

2 Answers

2
votes

Using window functions (MySQL-8.0, MariaDB-10.2), we DIV 600 to partition by the 10 minute (600 seconds) interval. We take the first in each group by id.

SELECT id, entrytime, RefrenceID
FROM ( 
  SELECT 
    id, entrytime, RefrenceID,
    ROW_NUMBER() OVER (PARTITION BY RefrenceID,UNIX_TIMESTAMP(entrytime) DIV 600 ORDER BY id) AS `rank`
  FROM timedata 
  ORDER BY id 
) AS tmp 
WHERE tmp.`rank` = 1
ORDER BY id, entrytime;

Ref: dbfiddle

0
votes
SELECT * 
FROM PriceHistory 
WHERE 
    `RefrenceID`=1001
    AND `TimeStamp` > (SELECT max(`TimeStamp`) FROM PriceHistory) - Interval 1440 Minute
    AND substring_index(TimeStamp,':',1)%5=0
GROUP BY `TimeStamp`;