def test_trunc_hour_func(self): start_datetime = microsecond_support(datetime(2015, 6, 15, 14, 30, 50, 321)) end_datetime = truncate_to(microsecond_support(datetime(2016, 6, 15, 14, 10, 50, 123)), 'hour') if settings.USE_TZ: start_datetime = timezone.make_aware(start_datetime, is_dst=False) end_datetime = timezone.make_aware(end_datetime, is_dst=False) self.create_model(start_datetime, end_datetime) self.create_model(end_datetime, start_datetime) self.assertQuerysetEqual( DTModel.objects.annotate(extracted=TruncHour('start_datetime')).order_by('start_datetime'), [ (start_datetime, truncate_to(start_datetime, 'hour')), (end_datetime, truncate_to(end_datetime, 'hour')), ], lambda m: (m.start_datetime, m.extracted) ) self.assertQuerysetEqual( DTModel.objects.annotate(extracted=TruncHour('start_time')).order_by('start_datetime'), [ (start_datetime, truncate_to(start_datetime.time(), 'hour')), (end_datetime, truncate_to(end_datetime.time(), 'hour')), ], lambda m: (m.start_datetime, m.extracted) ) self.assertEqual(DTModel.objects.filter(start_datetime=TruncHour('start_datetime')).count(), 1) with self.assertRaisesMessage(ValueError, "Cannot truncate DateField 'start_date' to DateTimeField"): list(DTModel.objects.annotate(truncated=TruncHour('start_date'))) with self.assertRaisesMessage(ValueError, "Cannot truncate DateField 'start_date' to DateTimeField"): list(DTModel.objects.annotate(truncated=TruncHour('start_date', output_field=DateField())))
def hourly_baseline_comparison(curr_date): today_min = datetime.datetime.combine(curr_date, datetime.time.min) today_max = datetime.datetime.combine(today_min, datetime.time.max) yesterday = curr_date-datetime.timedelta(days=1) daily_count = {} avg = [0]*24 date_now = timezone.now() oldest_day = (date_now-Metric.objects.all().order_by('date').first().date).days for i in range(1,oldest_day+1): curr_day = curr_date-datetime.timedelta(days=i) for city in settings.CITIES_ZIP_CODE:#count for each city daily count code_from = settings.CITIES_ZIP_CODE[city][0] code_to = settings.CITIES_ZIP_CODE[city][1] result = Metric.objects.filter(date__day=curr_day.day, date__month=curr_day.month, date__year=curr_day.year, postal_code__postal_code__range=(code_from,code_to)) if not city in daily_count: daily_count[city]=[{'date':curr_day,'value':len(result)}] else: daily_count[city].append({'date':curr_day,'value':len(result)}) result = Metric.objects.filter(date__day=curr_day.day, date__month=curr_day.month, date__year=curr_day.year) \ .annotate(hour=TruncHour('date')) \ .values('hour') \ .annotate(hour_count=Count('hour')) \ .values('hour','hour_count') for item in result: # import pdb;pdb.set_trace() avg[item['hour'].hour]+=item['hour_count'] avg = [item/(oldest_day) for item in avg] today_records = Metric.objects.filter(date__range=(today_min, today_max)) \ .annotate(hour=TruncHour('date')) \ .values('hour') \ .annotate(hour_count=Count('hour')) \ .values('hour','hour_count') # import pdb;pdb.set_trace() result_list = [0]*24 for i in range(len(today_records)): temp = { 'date':today_records[i]['hour'].strftime("%Y-%m-%dT%H:%M:%SZ"), 'value1':today_records[i]['hour_count'], 'value2':avg[today_records[i]['hour'].hour], 'previousDate':today_records[i]['hour'].strftime("%Y-%m-%dT%H:%M:%SZ") } result_list[today_records[i]['hour'].hour]=temp # print(daily_count) return result_list,daily_count
def plot(request): """Create json data for plot using Vega/Altair""" filtered = RequestFilter(request.GET, queryset=Request.objects.all()) data = (filtered.qs.annotate( hour=TruncHour("time")).values("hour").annotate( count=Count("id")).order_by()) df = pandas.DataFrame.from_records(data) def get_url(hour: pandas.Timestamp): """generate url to browse page""" nexthour = hour + datetime.timedelta(hours=1) return ( reverse("statistic_browse") + f"?time_after={hour.strftime('%d.%m.%Y %H:%M')}&time_before={nexthour.strftime('%d.%m.%Y %H:%M')}" ) df["url"] = df["hour"].apply(get_url) chart = (altair.Chart(df).mark_bar(color="green").encode( x=altair.X("hour:T", title="Zeitpunkt"), y=altair.Y("count:Q", title="Aufrufe pro Stunde"), href="url:N", tooltip=[ altair.Tooltip("hour", format="%d.%m. %H Uhr", title="Zeitpunkt"), altair.Tooltip("count", title="Aufrufe"), ], ).properties(width=1000)) return HttpResponse(chart.to_json(), content_type="application/json")
def groundhogs_by_hour_of_day(): result = RemovalsByLocation.objects.annotate( # hour=TruncHour('removal_time')).values('hour', 'sex',) \ hour=TruncHour('removal_time')).values('hour', ) \ .annotate(kills_per_hour=Count('id')) \ .order_by('hour') return result
def build_upcoming_srs_for_user(user): start, finish = get_24_hour_time_span() reviews = get_users_reviews(user).filter( next_review_date__range=(start, finish) ) reviews = ( reviews.annotate( hour=TruncHour("next_review_date", tzinfo=timezone.utc) ) .annotate(date=TruncDate("next_review_date", tzinfo=timezone.utc)) .values("date", "hour") .annotate(review_count=Count("id")) .order_by("date", "hour") ) logger.debug(f"Building upcoming SRS details for {user.username}") expected_hour = start.hour hours = [hour % 24 for hour in range(expected_hour, expected_hour + 24)] retval = OrderedDict.fromkeys(hours, 0) for review in reviews: found_hour = review["hour"].hour while found_hour != expected_hour: logger.debug(f"{found_hour} != {expected_hour}, skipping.") expected_hour = (expected_hour + 1) % 24 retval[expected_hour] = review["review_count"] logger.debug(f"Inserting reviews at hour {expected_hour}") real_retval = [value for key, value in retval.items()] return real_retval
def checkin_stats_api(request): timeseries = CheckIn.objects.all().annotate(hour=TruncHour('update_time')) \ .values('hour').annotate(checkins=Count('hour')) checkin_count = len(CheckIn.objects.all()) hacker_attrition_rate = { 'Attrition rate': attrition_rate(HackerApplication) } volunteer_attrition_rate = { 'Attrition rate': attrition_rate(VolunteerApplication) } mentor_attrition_rate = { 'Attrition rate': attrition_rate(MentorApplication) } sponsor_attrition_rate = { 'Attrition rate': attrition_rate(SponsorApplication) } return JsonResponse({ 'update_time': timezone.now(), 'timeseries': list(timeseries), 'checkin_count': checkin_count, 'hacker_attrition_rate': hacker_attrition_rate, 'volunteer_attrition_rate': volunteer_attrition_rate, 'mentor_attrition_rate': mentor_attrition_rate, 'sponsor_attrition_rate': sponsor_attrition_rate, })
def codes(self, request, server_pk=None, server_endpoint_pk=None): grouping = request.GET.get('grouping', 'month') qs_grouping = {'grouping': TruncMonth('date_added')} limit = 14 if grouping == 'hour': qs_grouping = {'grouping': TruncHour('date_added')} limit = 24 elif grouping == 'day': qs_grouping = {'grouping': TruncDay('date_added')} limit = 24 pings_qs = models.Ping.objects.filter(endpoint__id=server_endpoint_pk).order_by('grouping').annotate(**qs_grouping).values('grouping','response_code').annotate( Count('response_code') ).order_by("-grouping")[:24] pings = {} for ping in pings_qs: date = ping['grouping'].__str__() if pings.get(date) is None: pings[date] = { 'date': date, 'codes': {} } pings.get(date)['codes'][ ping['response_code'] ] = ping['response_code__count'] return Response(pings)
def to_representation(self, user): now = timezone.now() one_day_from_now = now + datetime.timedelta(hours=24) reviews = get_users_reviews(user).filter(next_review_date__range=(now, one_day_from_now)) \ .annotate(hour=TruncHour('next_review_date', tzinfo=timezone.utc)) \ .annotate(date=TruncDate('next_review_date', tzinfo=timezone.utc)) \ .values("streak", "date", "hour") \ .annotate(review_count=Count('id')).order_by("date", "hour") expected_hour = now.hour hours = [ hour % 24 for hour in range(expected_hour, expected_hour + 24) ] retval = OrderedDict.fromkeys(hours) for key in retval.keys(): retval[key] = OrderedDict.fromkeys( [level.name for level in KwSrsLevel], 0) for review in reviews: found_hour = review['hour'].hour while found_hour != expected_hour: expected_hour = (expected_hour + 1) % 24 streak = review['streak'] srs_level = STREAK_TO_SRS_LEVEL_MAP_KW[streak].name retval[expected_hour][srs_level] += review["review_count"] real_retval = [[count for srs_level, count in hourly_count.items()] for hour, hourly_count in retval.items()] return real_retval
def build_upcoming_srs_for_user(user): start, finish = get_24_hour_time_span() reviews = get_users_reviews(user).filter(next_review_date__range=(start, finish)) for review in reviews: logger.debug(review.next_review_date) reviews = reviews \ .annotate(hour=TruncHour('next_review_date', tzinfo=timezone.utc)) \ .annotate(date=TruncDate('next_review_date', tzinfo=timezone.utc)) \ .values("date", "hour") \ .annotate(review_count=Count('id')).order_by("date", "hour") expected_hour = start.hour hours = [hour % 24 for hour in range(expected_hour, expected_hour + 24)] retval = OrderedDict.fromkeys(hours, 0) for review in reviews: found_hour = review['hour'].hour while found_hour != expected_hour: logger.debug("{} != {}, skipping.".format(found_hour, expected_hour)) expected_hour = (expected_hour + 1) % 24 retval[expected_hour] = review["review_count"] logger.debug("Inserting reviews at hour {}".format(expected_hour)) real_retval = [value for key, value in retval.items()] return real_retval
def groundhogs_by_hour_of_day_by_sex(): result = RemovalsByLocation.objects.annotate( # hour=TruncHour('removal_time')).values('hour', 'sex',) \ hour=TruncHour('removal_time')).values('hour', 'sex', ) \ .annotate(kills_per_hour=Count('id')) \ .order_by('hour') # print("FUNCTION: {0}".format(result)) return result
def _group_by(queryset, group_field='winlogevent__created_on', group_by=GroupBy.HOUR): """ group the rows in a :class:`django.db.models.query.QuerySet` by a time sequence and `annotate` it with the time value See `Trunc <https://docs.djangoproject.com/en/2.2/ref/models/database-functions/"""\ """#trunc>`__ and `TimeField truncation <https://docs.djangoproject.com/en/2.2/ref/models/database-functions/"""\ """"#timefield-truncation>`__ in the `Django` docs. The resulting `queryset` will look something like this: ==== ================== =========== ============= ====================== site host count fails count success hour ==== ================== =========== ============= ====================== LGH lgh01.healthbc.org 0 14 Aug. 1, 2019, midnight LGH lgh01.healthbc.org 0 14 Aug. 1, 2019, 1 a.m. ==== ================== =========== ============= ====================== :arg queryset: the :class:`django.db.models.query.QuerySet` :arg str group_field: the name of the :class:`django.db.models.Model` field that contains time data; this field must be a :class:`django.db.models.DateTimeField` or a :class:`django.db.models.TimeField` field Default is 'winlogevent__created_on'. :arg group_by: group the data to be returned by a time sequence; default is :attr:`GroupBy.HOUR` :type group_by: :class:`GroupBy` :returns: a :class:`django.db.models.query.QuerySet` """ if group_by == GroupBy.NONE: return queryset # NOTE: This could be handled with a dictionary mapping from GroupBy values # to Trunc* classes. That was not pursued since there are only two # cases if group_by == GroupBy.MINUTE: trunc_obj = TruncMinute(group_field) elif group_by == GroupBy.HOUR: trunc_obj = TruncHour(group_field) else: raise ValueError(f'_group_by argument group_by must be one of ' f'{[gb.value for gb in GroupBy]}. ' f'Received {group_by}.') annotate_settings = {group_by.value: trunc_obj} return queryset.annotate(**annotate_settings).values(group_by.value)
def counts_by_hour(self): # Get list of dates return Pulse.objects.annotate( ts=TruncHour('created')).values('ts').distinct().annotate( num_events=Coalesce( models.Sum('count', filter=Q(host=self.ip)), 0), num_bytes=Coalesce(models.Sum('bytes', filter=Q(host=self.ip)), 0)).values( 'ts', 'host', 'num_events', 'num_bytes').order_by('ts').iterator()
def home(request): # COUNT MAIL emails = Mail.external_objects.all() email_count = emails.count() suspicious = (emails.filter( Q(tags__name__contains="suspicious") | Q(urls__tags__name__contains="suspicious") | Q(ips__tags__name__contains="suspicious") | Q(urls__domain__tags__name__contains="suspicious")).distinct().count( )) malicious = (emails.filter( Q(tags__name__contains="malicious") | Q(urls__tags__name__contains="malicious") | Q(ips__tags__name__contains="malicious") | Q(urls__domain__tags__name__contains="malicious")).distinct().count( )) qs = (Mail.external_objects.filter( submission_date__gte=timezone.now() - timedelta(days=10)).annotate( thour=TruncHour("submission_date")).order_by()) record_by_time = pivot( qs, "thour", "official_response", "pk", aggregation=Count, display_transform=lambda x: x.lower().replace(" ", "_"), ).order_by("thour") # PAGINATE LATEST EMAIL table = LatestMailTable( Mail.external_objects.prefetch_related( "addresses", "ips", "urls", "attachments", "tags", "addresses__tags", "ips__tags", "urls__tags", "attachments__tags", ).order_by("-submission_date")[:250], ) table.paginate(page=request.GET.get("page", 1), per_page=25) return render( request, "pages/main.html", { "table": table, "email_count": email_count, "suspicious": suspicious, "malicious": malicious, "groups": record_by_time, }, )
def get_date(date=None, user=1): if not date: date = timezone.localtime(timezone.now()).date() return functions.get_all_review(user=functions.get_user(user))\ .annotate(date=TruncDate('review_time'))\ .filter(date=date)\ .annotate(hour=TruncHour('review_time'))\ .values("hour")\ .annotate(count=Count('id'))\ .order_by("hour")
def votes_per_hour(cls, poll_id=None): """ Returns the quantity of votes by hour, if poll_id is set the return will be filtered by poll """ qs = cls.objects.annotate( hour=TruncHour('created_at')).values('hour').annotate( votes=models.Count('id')) if poll_id is not None: qs = qs.filter(poll_id=poll_id) return qs
def handle(self, *args, **options): cities = list( set(SensorLocation.objects.all().values_list( "city", flat=True).order_by("city"))) for city in cities: if not city: continue last_date_time = (SensorDataStat.objects.filter( city_slug=slugify(city)).values_list( "last_datetime", flat=True).order_by("-last_datetime")[:1]) if last_date_time: queryset = SensorDataValue.objects.filter( Q(sensordata__location__city__iexact=city), # Get dates greater than last stat calculation Q(created__gt=last_date_time), # Ignore timestamp values ~Q(value_type="timestamp"), # Match only valid float text Q(value__regex=r"^\-?\d+(\.?\d+)?$"), ) else: queryset = SensorDataValue.objects.filter( Q(sensordata__location__city__iexact=city), # Ignore timestamp values ~Q(value_type="timestamp"), # Match only valid float text Q(value__regex=r"^\-?\d+(\.?\d+)?$"), ) for stats in chunked_iterator( queryset.annotate(timestamp=TruncHour("created")).values( "timestamp", "value_type", "sensordata__sensor", "sensordata__location", "sensordata__sensor__node", ).order_by().annotate( last_datetime=Max("created"), average=Avg(Cast("value", FloatField())), minimum=Min(Cast("value", FloatField())), maximum=Max(Cast("value", FloatField())), sample_size=Count("created", FloatField()), ).filter( ~Q(average=float("NaN")), ~Q(minimum=float("NaN")), ~Q(maximum=float("NaN")), ).order_by("timestamp")): SensorDataStat.objects.bulk_create( list(map(lambda stat: map_stat(stat, city), stats)))
def get(self, request, *args, **kwargs): try: url, clicks = get_click_set(request, kwargs) except ShortenedUrl.DoesNotExist: return HttpResponse("does not exist") ts = clicks.annotate(t=TruncHour('timestamp')).values('t').annotate( y=Count('id')) # data = serializers.serialize("json", ts) data = [] for x in ts: data.append({"t": x["t"].isoformat(), "y": x["y"]}) logger.info(f"ts={ts} {data}") return JsonResponse(data, safe=False)
def get_action_properties(context): ads = Ad.objects.filter(approve=True) for ad in ads: context['actions'][ad] = {} action = {} clicks_count = 0 views_count = 0 clicks = Click.objects.filter(ad=ad).annotate( hour=TruncHour('time')).values('hour').annotate(clicks=Count('id')) views = View.objects.filter(ad=ad).annotate( hour=TruncHour('time')).values('hour').annotate(views=Count('id')) for v in views: action[v['hour']] = [ v['hour'] + timedelta(hours=1), 0, v['views'], 0 ] views_count += v['views'] for c in clicks: action[c['hour']][1] = c['clicks'] action[c['hour']][3] = c['clicks'] / action[c['hour']][2] clicks_count += c['clicks'] context['actions'][ad]['action'] = sorted(action.items(), reverse=True, key=lambda t: t[1]) context['actions'][ad]['cpv'] = clicks_count / views_count
def trendline(self, *args, **kwargs): # Get list of dates qs = Pulse.objects.annotate( ts=TruncHour('created')).values('ts').distinct().annotate( num_events=Coalesce(models.Sum('count', filter=Q(token=self)), 0), num_bytes=Coalesce(models.Sum('bytes', filter=Q(token=self)), 0)).values( 'ts', 'host', 'num_events', 'num_bytes').order_by('ts').iterator() data = [x['num_events'] for x in qs] if len(data) < 45: data = [0 for x in range(45 - len(data))] + data return ','.join([str(x) for x in data[-45:]])
def get_hourly_visits(self, start_date, end_date): current_results = self.trackers \ .filter(timestamp__gte=start_date, timestamp__lte=end_date) \ .exclude(type_device=Tracker.BOT) \ .exclude(referrer_url__contains=self.website_url) \ .annotate(month=TruncHour('timestamp')) \ .values('month') \ .annotate(requests=Count('pk')).order_by('-month') for item in current_results: item['t'] = '{date}' \ .format(date=item.pop('month')) item['y'] = item.pop('requests') return list(current_results)
def _group_by(queryset, group_field='winlogevent__created_on', group_by=GroupBy.NONE): """ group the rows in a :class:`django.db.models.query.QuerySet` by a time sequence and `annotate` it with the time value See `Trunc <https://docs.djangoproject.com/en/2.2/ref/models/database-functions/#trunc>`__ and `TimeField truncation <https://docs.djangoproject.com/en/2.2/ref/models/database-functions/#timefield-truncation>`__ in the `Django` docs. The resulting `queryset` will look something like this: ==== ================== =========== ============= ====================== site host count fails count success hour ==== ================== =========== ============= ====================== LGH lgh01.healthbc.org 0 14 Aug. 1, 2019, midnight LGH lgh01.healthbc.org 0 14 Aug. 1, 2019, 1 a.m. ==== ================== =========== ============= ====================== :arg queryset: the :class:`django.db.models.query.QuerySet` :arg str group_field: the name of the :class:`django.db.models.Model` field that contains time data; this field must be a :class:`django.db.models.DateTimeField` or a :class:`django.db.models.TimeField` field Default is 'winlogevent__created_on'. :arg group_by: group the data to be returned by a time sequence; default is :attr:`GroupBy.HOUR` :type group_by: :class:`GroupBy` :returns: a :class:`django.db.models.query.QuerySet` """ if group_by == GroupBy.HOUR: return queryset.\ annotate(hour=TruncHour(group_field)).values('hour') if group_by == GroupBy.MINUTE: return queryset.\ annotate(minute=TruncMinute(group_field)).values('minute') return queryset
def get_logins_by_event_state_borg_hour(now=None, time_delta=None): """ get the number of failed events and successful events during the interval defined by the arguments for each monitoring site aggregated by hour :arg datetime.datetime now: the initial moment By default the initial moment is the value returned by :meth:`django.utils.timezone.now` :arg datetime.timedelta time_delta: the time interval to consider By default, this will be retrieved from the dynamic preference `Dead if not seen for more than <../../../admin/dynamic_preferences/globalpreferencemodel/?q=dead_after>`__ :returns: a :class:`django.db.models.query.QuerySet` based on the :class:`citrus_borg.models.WinlogbeatHost` model """ if now is None: now = timezone.now() if time_delta is None: time_delta = get_preference('citrusborgcommon__dead_after') if not isinstance(now, datetime.datetime): raise TypeError('%s type invalid for %s' % (type(now), now)) if not isinstance(time_delta, datetime.timedelta): raise TypeError('%s type invalid for %s' % (type(time_delta), time_delta)) return WinlogbeatHost.objects.\ filter(winlogevent__created_on__gt=now - time_delta).\ annotate(hour=TruncHour('winlogevent__created_on')).values('hour').\ annotate( failed_events=Count( 'winlogevent__event_state', filter=Q(winlogevent__event_state__iexact='failed'))).\ annotate( successful_events=Count( 'winlogevent__event_state', filter=Q(winlogevent__event_state__iexact='successful'))).\ order_by('-hour', 'site__site')
def get(self, request): results = request.query_params if not isinstance(int(results['graph']), int): return Response({ 'err': '0', 'msg': 'Incorrect type for graph parameter' }) try: graph = Graph.objects.get(pk=int(results['graph'])) except Graph.DoesNotExist: return Response({'err': '1', 'msg': 'No graph with given id'}) if not request.user.is_authenticated and not graph.public: return Response({ 'err': '2', 'msg': 'This data cannot be viewed without authentication' }) data = [] for s in graph.selector.all(): arr = [] entries = DataEntry.objects.annotate( hour=TruncHour('timestamp')).filter( type=s.type, instance=s.instance).order_by('timestamp').all() last_hour = entries[0].timestamp.hour for e in entries: if e.timestamp.hour != last_hour: arr.append({ 'date': e.timestamp, 'value': round(e.value, 2) }) last_hour = e.timestamp.hour data.append(arr) return Response(data)
def get(self, request): """ Returns the average calls hour of day from the database. """ data = {"labels": [], "data": []} # Get all calls grouped by day and hour, ordered by hour day_calls = Call.objects.annotate( hour=TruncHour('received_timestamp'), day=TruncDay('received_timestamp'), ).values('day', 'hour').annotate(count=Count('pk')).order_by('hour') # Creates a list to hold the total calls for each hour of the day hours = [0] * 24 # Creates labels for each hour of the day labels = [] for i in range(0, 24): hour_label = str('0' + str(i) if i < 10 else i) + ':00' labels.append(hour_label) days = [] # The days that have been accounted for total_days = 0 # The total number of days in the data set # Iterate through each day-hour call total and add the count of calls # to the hours list. When encountering a new day value, increment the # total days (used for calculating the average) for call in day_calls: if call["day"] not in days: days.append(call["day"]) total_days += 1 hours[call["hour"].hour] += call["count"] # Calculate average over each day of results hours = [round(hour_count / total_days, 2) for hour_count in hours] # Add the data to the results list data["labels"] = labels data["data"] = hours # Return the JSON response return JsonResponse({"status": "true", "data": data})
def chart_tod(request): h_list = Homicide.objects.annotate( hour=TruncHour('time')).values('hour').annotate( ct=Count('count')).values('hour', 'ct', 'date', 'gender') data = alt.Data(values=list(h_list)) chart1 = alt.Chart(data, title='ToD (Time) vs Date Scatter').mark_circle( size=100).encode(alt.X('date:T'), alt.Y('hour:O'), color='gender:N', tooltip=['gender:N', 'ct:Q', 'hour:O', 'date:T']).interactive() chart2 = alt.Chart(data, title='ToD Summary').mark_bar().encode( x='count()', y=alt.Y('hour:O'), color='gender:N').properties(width=100) chart = alt.hconcat(chart1, chart2) return JsonResponse(chart.to_dict(), safe=False)
def measurements(request): start = request.GET.get("start", None) end = request.GET.get("end", None) interval = request.GET.get("interval", "minute") if start is not None: start = parser.parse(start) if end is not None: end = parser.parse(end) if start is None: start = datetime.datetime(1970, 1, 1) if end is None: end = datetime.datetime.now() measurements = Measurement.objects.filter( Q(timestamp__gte=start, timestamp__lte=end)) if interval == "hour": measurements = (measurements.annotate( datetime=TruncHour("timestamp")).values("datetime").annotate( pm25=Avg("pm25")).annotate(pm10=Avg("pm10")).annotate( co2=Avg("co2")).annotate( temperature=Avg("temperature")).annotate( humidity=Avg("humidity")).values( "datetime", "pm25", "pm10", "co2", "temperature", "humidity")) elif interval == "day": measurements = (measurements.annotate( datetime=TruncDay("timestamp")).values("datetime").annotate( pm25=Avg("pm25")).annotate(pm10=Avg("pm10")).annotate( co2=Avg("co2")).annotate( temperature=Avg("temperature")).annotate( humidity=Avg("humidity")).values( "datetime", "pm25", "pm10", "co2", "temperature", "humidity")) else: measurements = measurements.annotate(datetime=F("timestamp")).values( "datetime", "pm25", "pm10", "co2", "temperature", "humidity") return JsonResponse(list(measurements), encoder=DjangoJSONEncoder, safe=False)
def stats(self, request, server_pk=None, server_endpoint_pk=None): grouping = request.GET.get('grouping', 'month') qs_grouping = {'grouping': TruncMonth('date_added')} limit = 14 if grouping == 'hour': qs_grouping = {'grouping': TruncHour('date_added')} limit = 24 elif grouping == 'day': qs_grouping = {'grouping': TruncDay('date_added')} limit = 24 pings = models.Ping.objects.filter(endpoint__id=server_endpoint_pk).order_by('grouping').annotate(**qs_grouping).values('grouping').annotate( Max('response_time'), Min('response_time'), Avg('response_time'), Count('response_time'), Count('response_code') ).order_by("-grouping")[:24] return Response(pings)
def get(self, request, format=None): week_previous = timezone.now() - timedelta(days=7) tweets_by_hour = Tweet.objects.filter(created_at__gte=week_previous) \ .annotate(hour=TruncHour('created_at')) \ .values('hour').annotate(count=Count('id')) \ .values('hour', 'count').order_by('hour') most_retweeted = Tweet.objects.raw(""" select distinct on (date(created_at at time zone 'US/Central')) * from tweets_tweet where created_at > NOW() - INTERVAL '7 days' order by (date(created_at at time zone 'US/Central')), retweet_count desc; """) most_retweeted_by_day = [] for record in most_retweeted: most_retweeted_by_day.append(TweetSerializer(record).data) return Response({ 'tweets_by_hour': tweets_by_hour, 'most_retweeted_by_day': most_retweeted_by_day })
def checkin_stats_api(request): timeseries = CheckIn.objects.all().annotate(hour=TruncHour('update_time')) \ .values('hour').annotate(checkins=Count('hour')) checkin_count = len(CheckIn.objects.all()) applications = list(Application.objects.all()) attended = 0 confirmed = 0 for a in applications: if a.status == APP_CONFIRMED: confirmed += 1 if a.status == APP_ATTENDED: attended += 1 attrition_rate = { 'Attrition rate': attended * 100 / (confirmed + attended) } return JsonResponse({ 'update_time': timezone.now(), 'timeseries': list(timeseries), 'checkin_count': checkin_count, 'attrition_rate': attrition_rate })
def stats(request, checkplace: int): qs = Check.objects.filter(check_place=checkplace) if request.method == 'GET': qs = qs.filter(created_at__month=localtime().month) form = FilterForm( initial={'prec': request.META.get('HTTP_REFERER', '')} ) # quand on filtre, ça se rajoute à l'historique, donc c'est chiant pour les écrans de scan elif request.method == 'POST': form = FilterForm(request.POST) form.is_valid() qs = qs.filter( created_at__gte=form.cleaned_data.get('start', debut_ce_mois()), created_at__lte=form.cleaned_data.get('end', fin_ce_mois())) return render( request, 'checker/stats.html', { 'form': form, 'place': CheckPlace.objects.get(id=checkplace), 'checks': qs.annotate(hour=TruncHour('created_at')).values('hour').annotate( total=Count('id')).order_by('hour') })