def _fast_time_series(self,
start,
end,
interval='days',
date_field=None,
aggregate=None):
''' Aggregate over time intervals using just 1 sql query '''
date_field = date_field or self.date_field
aggregate = aggregate or self.aggregate
num, interval = _parse_interval(interval)
interval_s = interval.rstrip('s')
start, _ = get_bounds(start, interval_s)
_, end = get_bounds(end, interval_s)
kwargs = {'%s__range' % date_field: (start, end)}
# TODO: maybe we could use the tzinfo for the user's location
aggregate_data = self.qs.\
filter(**kwargs).\
annotate(d=Trunc(date_field, interval_s, tzinfo=start.tzinfo)).\
order_by().values('d').\
annotate(agg=aggregate)
today = _remove_time(compat.now())
def to_dt(d):
if isinstance(d, string_types):
return parse(d, yearfirst=True, default=today)
return d
data = dict((to_dt(item['d']), item['agg']) for item in aggregate_data)
stat_list = []
dt = start
while dt < end:
idx = 0
value = 0
for i in range(num):
value = value + data.get(dt, 0)
if i == 0:
stat_list.append((
dt,
value,
))
idx = len(stat_list) - 1
elif i == num - 1:
stat_list[idx] = (
dt,
value,
)
dt = dt + relativedelta(**{interval: 1})
return stat_list
def _fast_time_series(self, start, end, interval='days',
date_field=None, aggregates=None, engine=None):
''' Aggregate over time intervals using just 1 sql query '''
date_field = date_field or self.date_field
aggregates = self._get_aggregates(aggregates)
engine = engine or self._guess_engine()
num, interval = _parse_interval(interval)
start, _ = get_bounds(start, interval.rstrip('s'))
_, end = get_bounds(end, interval.rstrip('s'))
interval_sql = get_interval_sql(date_field, interval, engine)
kwargs = {'%s__range' % date_field : (start, end)}
aggregate_data = self.qs.extra(select = {'d': interval_sql}).\
filter(**kwargs).order_by().values('d')
for i, aggregate in enumerate(aggregates):
aggregate_data = aggregate_data.annotate(**{'agg_%d' % i: aggregate})
today = _remove_time(compat.now())
def to_dt(d):
if isinstance(d, basestring):
return parse(d, yearfirst=True, default=today)
return d
data = dict((to_dt(item['d']), [item['agg_%d' % i] for i in range(len(aggregates))]) for item in aggregate_data)
stat_list = []
dt = start
try:
try:
from django.utils.timezone import utc
except ImportError:
from django.utils.timezones import utc
dt = dt.replace(tzinfo=utc)
end = end.replace(tzinfo=utc)
except ImportError:
pass
zeros = [0 for i in range(len(aggregates))]
while dt < end:
idx = 0
value = []
for i in range(num):
value = map(lambda a, b: (a or 0) + (b or 0), value, data.get(dt, zeros[:]))
if i == 0:
stat_list.append(tuple([dt] + value))
idx = len(stat_list) - 1
elif i == num - 1:
stat_list[idx] = tuple([dt] + value)
dt = dt + relativedelta(**{interval : 1})
return stat_list
def _fast_time_series(self,
start,
end,
interval='days',
date_field=None,
aggregate=None,
engine=None):
''' Aggregate over time intervals using just 1 sql query '''
date_field = date_field or self.date_field
aggregate = aggregate or self.aggregate
engine = engine or self._guess_engine()
num, interval = _parse_interval(interval)
start, _ = get_bounds(start, interval.rstrip('s'))
_, end = get_bounds(end, interval.rstrip('s'))
interval_sql = get_interval_sql(date_field, interval, engine)
kwargs = {'%s__range' % date_field: (start, end)}
aggregate_data = self.qs.extra(select = {'d': interval_sql}).\
filter(**kwargs).order_by().values('d').\
annotate(agg=aggregate)
today = _remove_time(compat.now())
def to_dt(d):
if isinstance(d, str):
return parse(d, yearfirst=True, default=today)
return d.replace(tzinfo=None)
data = dict((to_dt(item['d']), item['agg']) for item in aggregate_data)
stat_list = []
dt = to_dt(start)
while dt < to_dt(end):
idx = 0
value = 0
for i in range(num):
value = value + data.get(dt, 0)
if i == 0:
stat_list.append((
dt,
value,
))
idx = len(stat_list) - 1
elif i == num - 1:
stat_list[idx] = (
dt,
value,
)
dt = dt + relativedelta(**{interval: 1})
return stat_list
def _fast_time_series(self, start, end, interval='days',
date_field=None, aggregate=None, engine=None):
''' Aggregate over time intervals using just 1 sql query '''
date_field = date_field or self.date_field
aggregate = aggregate or self.aggregate
engine = engine or self._guess_engine()
num, interval = _parse_interval(interval)
start, _ = get_bounds(start, interval.rstrip('s'))
_, end = get_bounds(end, interval.rstrip('s'))
interval_sql = get_interval_sql(date_field, interval, engine, timezone=start.tzinfo)
kwargs = {'%s__range' % date_field : (start, end)}
aggregate_data = self.qs.extra(select = {'d': interval_sql}).\
filter(**kwargs).order_by().values('d').\
annotate(agg=aggregate)
today = _remove_time(compat.now())
def to_dt(d):
if isinstance(d, str):
return parse(d, yearfirst=True, default=today)
return d.replace(tzinfo=start.tzinfo)
data = dict((to_dt(item['d']), item['agg']) for item in aggregate_data)
arithmetic_methods = {Min: min, Max: max, Count: operator.add}
aggregate_method = arithmetic_methods[aggregate.__class__] if aggregate.__class__ in arithmetic_methods else operator.add
stat_list = []
dt = start
if data:
default_value = list(data.values())[0].__class__()
else:
default_value = 0
while dt < end:
idx = 0
value = default_value
for i in range(num):
value = aggregate_method(value, data.get(dt, default_value))
if i == 0:
if aggregate_method == min:
value = data.get(dt, 0)
stat_list.append((dt, value,))
idx = len(stat_list) - 1
elif i == num - 1:
stat_list[idx] = (dt, value,)
dt = dt + relativedelta(**{interval : 1})
return stat_list
def _fast_time_series(self, start, end, interval='days',
date_field=None, aggregate=None, engine=None):
''' Aggregate over time intervals using just 1 sql query '''
date_field = date_field or self.date_field
aggregate = aggregate or self.aggregate
engine = engine or self._guess_engine()
num, interval = _parse_interval(interval)
start, _ = get_bounds(start, interval.rstrip('s'))
_, end = get_bounds(end, interval.rstrip('s'))
interval_sql = get_interval_sql(date_field, interval, engine)
kwargs = {'%s__range' % date_field : (start, end)}
aggregate_data = self.qs.extra(select = {'d': interval_sql}).\
filter(**kwargs).order_by().values('d').\
annotate(agg=aggregate)
today = _remove_time(compat.now())
def to_dt(d):
# Fix from https://bitbucket.org/aztrock/django-qsstats-magic
try:
if isinstance(d, basestring):
return parse(d, yearfirst=True, default=today)
return d
except:
if isinstance(d, str):
return parse(d, yearfirst=True, default=today)
return d
data = dict((to_dt(item['d']), item['agg']) for item in aggregate_data)
stat_list = []
dt = start
while dt < end:
idx = 0
value = 0
for i in range(num):
value = value + data.get(dt, 0)
if i == 0:
stat_list.append((dt, value,))
idx = len(stat_list) - 1
elif i == num - 1:
stat_list[idx] = (dt, value,)
dt = dt + relativedelta(**{interval : 1})
return stat_list
def test_until(self):
now = compat.now()
today = _remove_time(now)
yesterday = today - datetime.timedelta(days=1)
u = User.objects.create_user('u', '*****@*****.**')
u.date_joined = today
u.save()
qs = User.objects.all()
qss = QuerySetStats(qs, 'date_joined')
self.assertEqual(qss.until(now), 1)
self.assertEqual(qss.until(today), 1)
self.assertEqual(qss.until(yesterday), 0)
self.assertEqual(qss.until_now(), 1)
def test_until(self):
now = compat.now()
today = _remove_time(now)
yesterday = today - datetime.timedelta(days=1)
u = User.objects.create_user('u', '*****@*****.**')
u.date_joined = today
u.save()
qs = User.objects.all()
qss = QuerySetStats(qs, 'date_joined')
self.assertEqual(qss.until(now), 1)
self.assertEqual(qss.until(today), 1)
self.assertEqual(qss.until(yesterday), 0)
self.assertEqual(qss.until_now(), 1)
def test_after(self):
now = compat.now()
today = _remove_time(now)
tomorrow = today + datetime.timedelta(days=1)
u = User.objects.create_user('u', '*****@*****.**')
u.date_joined = today
u.save()
qs = User.objects.all()
qss = QuerySetStats(qs, 'date_joined')
self.assertEqual(qss.after(today), 1)
self.assertEqual(qss.after(now), 0)
u.date_joined = tomorrow
u.save()
self.assertEqual(qss.after(now), 1)
def test_after(self):
now = compat.now()
today = _remove_time(now)
tomorrow = today + datetime.timedelta(days=1)
u = User.objects.create_user('u', '*****@*****.**')
u.date_joined = today
u.save()
qs = User.objects.all()
qss = QuerySetStats(qs, 'date_joined')
self.assertEqual(qss.after(today), 1)
self.assertEqual(qss.after(now), 0)
u.date_joined=tomorrow
u.save()
self.assertEqual(qss.after(now), 1)
def _remove_time(dt):
tzinfo = getattr(dt, 'tzinfo', compat.now().tzinfo)
return datetime.datetime(dt.year, dt.month, dt.day, tzinfo=tzinfo)
def update_today(self):
_now = compat.now()
self.today = _remove_time(_now)
return self.today
def after_now(self, date_field=None, aggregate=None):
return self.pivot(compat.now(), 'gte', date_field, aggregate)
def until_now(self, date_field=None, aggregate=None):
return self.pivot(compat.now(), 'lte', date_field, aggregate)
def update_today(self):
_now = compat.now()
self.today = _remove_time(_now)
return self.today
def after_now(self, date_field=None, aggregate=None):
return self.pivot(compat.now(), 'gte', date_field, aggregate)
def until_now(self, date_field=None, aggregate=None):
return self.pivot(compat.now(), 'lte', date_field, aggregate)
def after_now(self, date_field=None, aggregates=None):
return self.pivot(compat.now(), 'gte', date_field, self._get_aggregates(aggregates))
def _remove_time(dt):
tzinfo = getattr(dt, 'tzinfo', compat.now().tzinfo)
return datetime.datetime(dt.year, dt.month, dt.day, tzinfo=tzinfo)
def test_time_series(self):
_now = compat.now()
today = _remove_time(_now)
self.assertTimeSeriesWorks(today)
def test_time_series(self):
_now = compat.now()
today = _remove_time(_now)
self.assertTimeSeriesWorks(today)