0

I have table like this: 
id | name | time     |
1  | aaaa | 00:36:00 |
2  | aaaa | 01:00:00 |
3  | cccc | 00:10:00 |
4  | bbbb | 00:30:00 |
5  | cccc | 00:30:00 |

How can I count the time grouped for each name in Django like this:

name | time     |
aaaa | 01:36:00 |
bbbb | 00:30:00 |
cccc | 00:40:00 |

It is possible in Django ??
Thanks for any help!

mir_kol
  • 89
  • 8

2 Answers2

1

If you want to make the grouping(Django Docs) by exact time, then you can simple aggregate like this:

from django.db.models import Count
Model.objects.all().values("time").annotate(Count("id")).order_by()

То aggregate DateTimeField and TimeField we can use also Trunc(Django docs) function. Truncates a date up to a significant component.

class Trunc(expression, kind, output_field=None, tzinfo=None, is_dst=None, **extra)

Trunc takes a single expression, representing a DateField, TimeField, or DateTimeField, a kind representing a date or time part, and an output_field that’s either DateTimeField(), TimeField(), or DateField().

Specific for TimeField use TruncHour, TruncMinute or TruncSecond see Django Docs - Usage example:

>>> from datetime import date, datetime
>>> from django.db.models import Count
>>> from django.db.models.functions import (
...     TruncDate, TruncDay, TruncHour, TruncMinute, TruncSecond,
... )
>>> from django.utils import timezone
>>> import pytz
>>> start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc)
>>> Experiment.objects.create(start_datetime=start1,start_date=start1.date())
>>> melb = pytz.timezone('Australia/Melbourne')
>>> Experiment.objects.annotate(
...     date=TruncDate('start_datetime'),
...     day=TruncDay('start_datetime', tzinfo=melb),
...     hour=TruncHour('start_datetime', tzinfo=melb),
...     minute=TruncMinute('start_datetime'),
...     second=TruncSecond('start_datetime'),
... ).values('date', 'day', 'hour', 'minute', 'second').get()
{'date': datetime.date(2014, 6, 15),
 'day': datetime.datetime(2014, 6, 16, 0, 0, tzinfo=<DstTzInfo 'Australia/Melbourne' AEST+10:00:00 STD>),
 'hour': datetime.datetime(2014, 6, 16, 0, 0, tzinfo=<DstTzInfo 'Australia/Melbourne' AEST+10:00:00 STD>),
 'minute': 'minute': datetime.datetime(2014, 6, 15, 14, 30, tzinfo=<UTC>),
 'second': datetime.datetime(2014, 6, 15, 14, 30, 50, tzinfo=<UTC>)
}
NKSM
  • 5,422
  • 4
  • 25
  • 38
  • Thanks, I'll try to check it tomorrow. – mir_kol Mar 24 '21 at 23:11
  • @mir_kol, to make the grouping by exact time use this: `Model.objects.all().values("time").annotate(Count("id")).order_by()`. I edited my answer. – NKSM Mar 25 '21 at 02:15
0

It's hard to say without looking into your model, but I'm quite sure you can do something like this:

from django.db.models import Sum


MyModel.objects.filter(...).annotate(time_sum=Sum("time"))

Take a look at Django's aggregate documentation.

Murilo Sitonio
  • 270
  • 7
  • 30
  • Yes, my example was very general but your method works well... almost. Time is presented in decimal format - example '19000.000000' and it is 02:30:00. I was try to convert like there [link](https://stackoverflow.com/questions/32087209/converting-decimal-time-hh-hhh-into-hhmmss-in-python) but it doesn't work ... Have all time something like this... 19000:00:00 – mir_kol Mar 24 '21 at 23:22
  • Hm... actually it counting but how? or how to convert it to time format?? **Examples:** 1. 01:45:00, 00:40:00 - should be 02:25:00 but is 18500.000000 ; 2. 00:30:00, 01:15:00, 00:45:00, 00:40:00 should be 03:10:00 but is 23000.000000 – mir_kol Mar 28 '21 at 18:38