eggbert eggbert - 1 year ago 77
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.

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


It outputs the following SQL as the generated query:

FROM "incoming_hc_data"
WHERE django_datetime_extract('minute', "incoming_hc_data"."utctime", GMT) = 0

2ps 2ps
Answer Source

Use the extra function:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download