4

I have a table with metadata on fieldmeasurement in which a fields 'startDate' is of type datetime. The value can be empty, when no info is available. I would like to run some statistics on this field, and get min and max value (i.e. the oldest measurements available over all records). No problem for the Max, but for Min the aggregation returns None (the value in empty fields).

So my idea is to filter these records from the queryset, before running the aggregate. I cannot find how to do this.

I have tried

oldestdate = Measurement.object.filter(startDate__isNull=True).aggregate(Min('startDate'))['startDate__min']

Any idea what's wrong, or how to do this?

EDIT: some more testing reveals that:

Measurement.objects.filter(startDate_isnull=True) -> only records with startDate = None
Measurement.objects.filter(startDate_isnull=False) -> all records, also the ones with startDate = None. 
Measurement.objects.exclude(startDate__isnull=False) -> only records with startDate = None
Measurement.objects.exclude(startDate__isnull=True) -> shows all records (also startDate == None)
Bart P.
  • 849
  • 1
  • 8
  • 11

1 Answers1

4
oldestdate = Measurement.objects.filter(
    startDate__isnull=True).aggregate(min_date=Min('startDate'))

# or to make it small:

oldestdate = Measurement.objects.filter(startDate__isnull=True
    ).aggregate(min_date=Min('startDate'), max_date=Max('startDate'))
Rob Bednark
  • 25,981
  • 23
  • 80
  • 125
catherine
  • 22,492
  • 12
  • 61
  • 85
  • It does not work. Fields with None are not filtered out. oQuery = WindWaveFile.objects.all() oQuery.exclude(startDate__isnull=True).aggregate(Min('startDate'))['startDate__min'].strftime("%d/%m/%Y %H:%M"). The strftime raises an exception because of that (error 'NoneType' object has no attribute 'strftime' ) – Bart P. Mar 11 '13 at 12:07
  • correct, in the version I posted before, the strftime part was not yet added. However, the original problem remains, the filter does not recognize None. – Bart P. Mar 11 '13 at 12:16
  • ass a bypass I now filter everything since a certain date (10years ago) and this works (and I can use the strftime). But it is not a clean solution (as I should never be 100% sure that all dates are younger than 10years) – Bart P. Mar 11 '13 at 12:19
  • Try to test like this `oldestdate = Measurement.objects.filter(startDate__isnull=True)`, if None is filtered or not. In that way will know what's the problem – catherine Mar 11 '13 at 12:19
  • other function is change when django upgrade to latest version – catherine Mar 11 '13 at 12:20
  • Good, next if you want to debug something just divide it and test it one by one until all are working :) – catherine Mar 11 '13 at 12:56
  • thanks catherine. some more testing reveals that startDate_isnull=True shows only records with startDate = None, but startDate_isnull=False does not do the opposite. i.e. it returns all records, also the ones with startDate = None. But to my surprise switching to exclude does the opposite. exclude(startDate__isnull=False) shows only records with startDate=None, while exclude(startDate__isnull=True) shows all records (also startDate == None) – Bart P. Mar 11 '13 at 12:59
  • 1
    yeah filter() and exclude() do the opposite thing – catherine Mar 11 '13 at 13:03