0

I'm creating a database (in MySQL) with a table of measurements. For each measurement I want to store the DateTime it came in. For showing plots within an app for different intervals (measurements of the day/week/month/year) I want sample the data points I have, so I can return e. g. 30 data points for the whole year as well as for the day/hour. This is the same as done with stock price graphs:

stock price plot for 1 day vs stock price plot for 1 month

As you can see, the amount of data points is the same in both pictures.

So how can I select x entries within a timespan in MySQL via SQL?

My data looks like this:

+====+====================+=============+==========+
| id | datetime           | temperature | humidity |
+====+====================+=============+==========+
| 1  | 1-15-2016 00:30:00 | 20          | 40       |
+----+--------------------+-------------+----------+
| 2  | 1-15-2016 00:35:00 | 19          | 41       |
+----+--------------------+-------------+----------+
| 3  | 1-15-2016 00:40:00 | 20          | 40       |
+----+--------------------+-------------+----------+
| 4  | 1-15-2016 00:45:00 | 20          | 42       |
+----+--------------------+-------------+----------+
| 5  | 1-15-2016 00:50:00 | 21          | 42       |
+----+--------------------+-------------+----------+
| 6  | 1-15-2016 00:55:00 | 20          | 43       |
+----+--------------------+-------------+----------+
| 7  | 1-15-2016 01:00:00 | 21          | 43       |
+====+====================+=============+==========+

Let's say, I always want two data points (in reality a lot more). So for the last half hour I want the database to return data point 1 and 4, for the last ten minutes I want it to return 6 and 7.

Thanks for helping!

PS: I'm sorry for any errors in my English

Julian Aßmann
  • 190
  • 1
  • 13
  • 1
    I think sample data and desired results would clarify what you are really trying to do. – Gordon Linoff Mar 15 '17 at 20:16
  • From the DateTime, you can group by day/week/month/year – Ibu Mar 15 '17 at 20:23
  • Do you want to *sample* from your overall data, or do you perhaps want to group and aggregate it (e.g. take the average, maximum, or minimum in each time period)? – John Bollinger Mar 15 '17 at 20:24
  • I want to sample from my overall data. – Julian Aßmann Mar 15 '17 at 20:28
  • There are many more data points in the "for 1 day" graph than in the "for one month graph". In any case, do you want the same amount of rows per period? You mention 2 points for 30 minutes or 2 points per ten minutes. Or do you always want 2 points from any period? Do you want the points to be selected systematically (e.g. first and last points in the period) or randomly? If it is random, then you can get different results with each selection. – Degan Mar 15 '17 at 22:56

2 Answers2

0

OK, assuming a very simple systematic approach, you can get the first and last entry for any defined period:

select *
from table
where mydatetime =
   (select 
    max(mydatetime)
    from table
    where mydatetime between '2017-03-01' and '2017-03-15'
    )
OR mydatetime =
   (select 
    min(mydatetime)
    from table
    where mydatetime between '2017-03-01' and '2017-03-15'
    )
Degan
  • 989
  • 2
  • 16
  • 30
  • Thanks, but I already knew that. I want to sample the data points, I want to say "I want 30 equally distributed data points out of that interval". – Julian Aßmann Mar 15 '17 at 20:40
-1

I believe your answer can be found at the following location:

https://stackoverflow.com/a/1891796/7176046

If you are looking to filter out any items not within your date/time your query would use:
Select * from table where Date/Time is (What you want to sort by)

Community
  • 1
  • 1
vanblart
  • 328
  • 2
  • 12