eggbert eggbert - 1 month ago 12
MySQL Question

Reducing the amount of results from a Django query by selecting every Nth row

I am trying to reduce the amount of events I get from a query on a table in mysql which has a lot of events stored in it. There is roughly one event each minute, each event has a datetime and then some other sensor readings. I would like to reduce the amount of data so that I'm only getting one reading every hour or so.
I realise I can do something like:

IncomingData.objects.filter(utctime__range=('2016-10-07', '2016-10-14'))[::60]


This will give me 1 event an hour (assuming they are ordered by time?) but this is still returning 60 events per hour from the database.

Potentially I might want to read a bigger date range and less events - for instance one event a day over an entire year, and this method wouldn't work because it would be reading millions of unnecessary rows.

I have seen some solutions using ROWNUM but I want to keep away from raw sql if possible (e.g. http://dba.stackexchange.com/a/56389)

I have also tried the following which I would have thought would return the first event each hour but it returns an empty queryset:

IncomingHcData.objects.filter(utctime__minute=0)


It outputs the following SQL as the generated query:

SELECT
"incoming_hc_data"."uid",
"incoming_hc_data"."utctime",
"incoming_hc_data"."temp_1",
"incoming_hc_data"."temp_2",
"incoming_hc_data"."temp_3",
FROM "incoming_hc_data"
WHERE django_datetime_extract('minute', "incoming_hc_data"."utctime", GMT) = 0

2ps 2ps
Answer

Use the extra function:

IncomingData.objects.extra(where=['minute(utctime)=0'])