Python django.db.models.functions.TruncDate() Examples

def get_average_execution_time_grouped(self, from_date: datetime.datetime,
                                           to_date: datetime.datetime) -> Optional[datetime.timedelta]:
        return self.select_related(
            'ethereum_tx', 'ethereum_tx__block'
        ).exclude(
            ethereum_tx__block=None,
        ).annotate(
            interval=Cast(F('ethereum_tx__block__timestamp') - F('created'),
                          output_field=DurationField())
        ).filter(
            created__range=(from_date, to_date)
        ).annotate(
            created_date=TruncDate('created')
        ).values(
            'created_date'
        ).annotate(
            average_execution_time=Avg('interval')
        ).values('created_date', 'average_execution_time').order_by('created_date') 

def get_tokens_usage_grouped(self) -> Optional[List[Dict[str, Any]]]:
        """
        :return: List of Dict 'gas_token', 'total', 'number', 'percentage'
        """
        return SafeMultisigTx.objects.annotate(
            date=TruncDate('created')
        ).annotate(
            number=Window(expression=Count('*'),
                          partition_by=[F('gas_token'), F('date')]),
            percentage=100.0 * Window(expression=Count('*'),
                                      partition_by=[F('gas_token'),
                                                    F('date')]
                                      ) / Window(expression=Count('*'),
                                                 partition_by=[F('date')])
        ).values(
            'date', 'gas_token', 'number', 'percentage'
        ).distinct().order_by('date') 

def get_relay_history_stats(self, from_date: datetime.datetime = None,
                                to_date: datetime.datetime = None) -> Dict[str, Any]:

        from_date = from_date if from_date else datetime.datetime(2018, 11, 1, tzinfo=utc)
        to_date = to_date if to_date else timezone.now()

        def add_time_filter(queryset):
            return queryset.filter(created__range=(from_date, to_date))

        return {
            'safes_created': {
                'deployed': add_time_filter(SafeContract.objects.deployed()).annotate(
                    created_date=TruncDate('created')).values('created_date').annotate(number=Count('*')
                                                                                       ).order_by('created_date'),
                # 'average_deploy_time_seconds': SafeContract.objects.get_average_deploy_time_grouped(from_date, to_date),
                # 'average_deploy_time_total_seconds':
                #    SafeContract.objects.get_average_deploy_time_total_grouped(from_date, to_date),
                'payment_tokens': SafeContract.objects.get_creation_tokens_usage_grouped(from_date, to_date),
            },
            'relayed_txs': {
                'total': add_time_filter(SafeMultisigTx.objects.annotate(
                    created_date=TruncDate('created')).values('created_date').annotate(number=Count('*')
                                                                                       ).order_by('created_date')),
                'average_execution_time_seconds': SafeMultisigTx.objects.get_average_execution_time_grouped(from_date,
                                                                                                            to_date),
                'payment_tokens': add_time_filter(SafeMultisigTx.objects.get_tokens_usage_grouped()),
            }
        } 

def helpers(request, event_url_name, job_pk=None):
    event = get_object_or_404(Event, url_name=event_url_name)

    # helpers of one job
    if job_pk:
        job = get_object_or_404(Job, pk=job_pk)

        # check permission
        if not job.is_admin(request.user):
            return nopermission(request)

        is_admin = event.is_admin(request.user)

        shifts_by_day = job.shifts_by_day().items()

        # show list of helpers
        context = {'event': event,
                   'job': job,
                   'shifts_by_day': shifts_by_day,
                   'is_admin': is_admin}
        return render(request, 'registration/admin/helpers_for_job.html',
                      context)

    # check permission
    if not event.is_involved(request.user):
        return nopermission(request)

    # list of days with shifts
    days = Shift.objects.filter(job__event=event) \
        .annotate(day=TruncDate('begin')).values_list('day', flat=True) \
        .order_by('day').distinct()

    # overview over jobs
    context = {'event': event,
               'days': days}
    return render(request, 'registration/admin/helpers.html', context) 

def get_requests_count(queryset):
    """
    This function returns a list of dictionaries containing each one the
    requests count per minute of a certain ``Tracker``s queryset.

    :param queryset: A Django QuerySet of ``Tracker``s.
    :return: List of dictionaries with the requests count per minute.
    """
    return queryset.annotate(
        date=TruncDate('timestamp'),
        hour=Extract('timestamp', 'hour'),
        minute=Extract('timestamp', 'minute')
    ).values(
        'date', 'hour', 'minute'
    ).annotate(requests=Count('pk')) 

def get_queryset(self):

        # Count all the issues per day
        queryset = (
            Issue.objects.annotate(date=TruncDate("created"))
            .values("date")
            .annotate(total=Count("id"))
        )

        # Filter by repository
        repository = self.request.query_params.get("repository")
        if repository:
            queryset = queryset.filter(diff__revision__repository__slug=repository)

        # Filter by analyzer
        analyzer = self.request.query_params.get("analyzer")
        if analyzer:
            queryset = queryset.filter(analyzer=analyzer)

        # Filter by check
        check = self.request.query_params.get("check")
        if check:
            queryset = queryset.filter(check=check)

        # Filter by date
        since = self.request.query_params.get("since")
        if since is not None:
            try:
                since = datetime.strptime(since, "%Y-%m-%d").date()
            except ValueError:
                raise APIException(detail="invalid since date - should be YYYY-MM-DD")
            queryset = queryset.filter(date__gte=since)

        return queryset.order_by("date")


# Build exposed urls for the API 

def museum_view(request):
  def convert_timedelta(td):
    return {
      'year': td.days // 365,
      'day': td.days % 365,
      'hour': td.seconds // 3600,
      'minute': (td.seconds % 3600) // 60,
      'second': td.seconds % 60
    }

  ctx = {}
  ctx['total_problem_count'] = Problem.objects.count()
  ctx['total_submission_count'] = Submission.objects.count()
  ctx['total_user_count'] = User.objects.filter(is_active=True).count()
  # NOTE: this will break if there is no submission at all
  first_submission = Submission.objects.last()
  ctx['first_submission_time'] = first_submission.create_time
  ctx['first_submission_duration'] = convert_timedelta(datetime.now() - ctx['first_submission_time'])
  ctx['first_submission_author'] = first_submission.author

  from uptime import uptime
  ctx['uptime'] = convert_timedelta(timedelta(seconds=uptime()))
  ctx['server_time'] = datetime.now()
  ctx['eoj3_create_duration'] = convert_timedelta(datetime.now() - datetime(2017, 3, 11, 18, 32))

  ctx['submission_count_1'] = Submission.objects.filter(create_time__gt=datetime.now() - timedelta(days=1)).count()
  ctx['submission_count_7'] = Submission.objects.filter(create_time__gt=datetime.now() - timedelta(days=7)).count()
  ctx['submission_count_30'] = Submission.objects.filter(create_time__gt=datetime.now() - timedelta(days=30)).count()

  ctx['submission_stat'] = Submission.objects.filter(create_time__gt=datetime.today() - timedelta(days=30)). \
    annotate(date=TruncDate('create_time')).values('date'). \
    annotate(count=Count('id')).values('date', 'count').order_by()
  ctx['user_stat'] = User.objects.filter(is_active=True).annotate(date=TruncYear('date_joined')).values('date'). \
    annotate(count=Count('id')).values('date', 'count').order_by("date")
  for idx, user in enumerate(ctx['user_stat']):
    if idx == 0:
      continue
    user['count'] += ctx['user_stat'][idx - 1]['count']
  ctx['problem_stat'] = Problem.objects.annotate(date=TruncYear('create_time')).values('date'). \
    annotate(count=Count('id')).values('date', 'count').order_by("date")
  for idx, user in enumerate(ctx['problem_stat']):
    if idx == 0:
      continue
    user['count'] += ctx['problem_stat'][idx - 1]['count']

  ctx['servers'] = servers = Server.objects.filter(enabled=True)

  for server in servers:
    server.status = ping(server)

  return render(request, 'museum.jinja2', context=ctx)