Average age from birth date

In my django(3.0.5) application I am trying to get average age from date of birth in a model. In MySql database table birth_date stores data in ‘1980-01-15’ format. Here is how I tried:

class ShippingStaff(models.Model):
    full_name = models.CharField('Full Name', max_length=200)
    birth_date = models.DateField('Date of Birth', null=True, blank=True)

def age(bday, d=None):
    bday = datetime.strptime(bday, '%Y-%m-%d')
    if d is None:
        d = datetime.date.today()
    return (d.year - bday.year) - int((d.month, d.day) < (bday.month, bday.day))

def home(request):
shipping_staff = ShippingStaff.objects.aggregate(staff_count=Count('full_name'),
Exception Type: ValueError
Exception Value: time data ‘birth_date’ does not match format ‘%Y-%m-%d’

How can I solve this? Any help will be much appreciated.

Depending upon how precise I needed to be, my first thought would be to use the MySql-provided datediff function to calculate each person’s age in days and average them, then convert that to a year-month-day value. What I don’t know is how I would code that in terms of an ORM query. I know you can define custom functions for the ORM to use in building queries, but I’ve never done it myself.

As a side note - and it’s perhaps a little pedantic - but MySql stores a date field internally as a 3-byte value. MySql translates it to the YYYY-MM-DD format when being selected.

Thanks for the hint.
I ended up with a solution below:

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("select ROUND(AVG(DATEDIFF(CURDATE(), birth_date) / 365.25), 2) as avg_age 
                            FROM crewdatabd_shippingstaff")
    age = cursor.fetchone()

in template:
1 Like
from django.db.models.functions import ExtractYear
from django.utils import timezone
current_year = timezone.now().year
ShippingStaff.objects.aggregate(average_age=Avg(current_year - ExtractYear('birth_date')))

You could just get the DB to calculate the current year in the same way but my guess is it is probably more efficient this way