Get year from Django DateTimeField with values()

Question

What is the correct way to extract the year from a DateTimeField?

Example given:

class Article(models.Model):
    title = models.CharField(_('title'), max_length=250)
    slug = models.SlugField(_('slug'), max_length=250, unique=True, default='', blank=True)
    content = models.TextField(_('content'))
    author = models.ForeignKey(settings.AUTH_USER_MODEL)
    categories = models.ManyToManyField(Category)
    tags = models.ManyToManyField(Tag)
    created = models.DateTimeField(_('created'), default=timezone.now)
    publish_date = models.DateTimeField(_('publish date'), blank=True, null=True)

    STATUS_ARTICLE = (
        ('DRAFT', _('draft')),
        ('PUBLISHED', _('published'))
    )

    status = models.CharField(_('status'), max_length=100, choices=STATUS_ARTICLE, default='DRAFT')

class ExtractMonth(Func):
    template = "EXTRACT(MONTH FROM %(expressions)s)"

    def __init__(self, *expressions, **extra):
        extra['output_field'] = models.SmallIntegerField()
        super().__init__(*expressions, **extra)

Trying to get a list of all the years, and number of articles per year:

result = Article.objects.filter(status='PUBLISHED').annotate(Year=ExtractYear('publish_date')).values('Year').annotate(dcount=Count('Year'))

This results in the following error:

near "FROM": syntax error

The resulting query is:

SELECT EXTRACT(YEAR FROM "articles_article"."publish_date") AS "Year", COUNT(EXTRACT(YEAR FROM "articles_article"."publish_date")) AS "dcount" FROM "articles_article" WHERE "articles_article"."status" = PUBLISHED GROUP BY EXTRACT(YEAR FROM "articles_article"."publish_date"), "articles_article"."created" ORDER BY "articles_article"."created" DESC

Answer 1

This is what I've ended up doing. Gocht's solution looks like it should work but I had problems (Django 1.10, SQLite) with Django handling the results. I didn't want to use RawSQL but that's the only thing I've got to work.

try:
    from django.db.models.functions import ExtractYear
except ImportError:
    from django.db.models.expressions import RawSQL

qs = Article.objects.filter(...)

try:
    # Django >= 1.10
    qs = qs.annotate(Year=ExtractYear('publish_date'))
except NameError:
    # Django < 1.10
    qs = qs.annotate(Year=RawSQL(
            "CAST(SUBSTR('table_name'.'publish_date', 1, 4) AS integer)",
            ()
         ))

results = qs.values('Year').annotate(dcount=Count('Year'))

This makes Year an integer, rather than a string; remove the CAST(... AS integer) if you want a string.

You'll need to change the table_name to be whatever your model's database table is.

I'm not sure the except NameError is the best way to handle that, but I couldn't work out how better to handle this.

Answer 2

I found this question a lot interesting. So I was playing with console for a while.

I followed the links provided by @Ivan and got this:

from django.db.models import F, Func
from django.db.models.functions import Substr

Article.objects.filter(...).annotate(_date=Func(F('publish_date'), function='LOWER'))
                           .annotate(publish_year=Substr('_date', 1, 4))
                           .values('publish_year')

This should give your the year as an string.

Note: this will work if in _date you get something like this: u'2015-08-24 09:45:16', if you get a different string, you can change the indexes in Substr('_date', 1, 4). You can see what kind of string you get in _date by adding it in .values('_date', 'publish_year').

I hope this helps.

Extra:

This is the result I got:

[{'date': datetime.datetime(2015, 8, 24, 9, 45, 16), 'date3': u'2015', 'date2': u'2015-08-24 09:45:16'}, ...]

In this case, date3 is for me the final result.

EDIT:

Generated SQL:

>>> print MyModel.objects.all().annotate(date2=Func(F('date'), function='LOWER')).annotate(date3=Substr('date2', 1, 4)).query
SELECT `app_model`.`id`, `app_model`.`date`, LOWER(`app_model`.`date`) 
AS `date2`, SUBSTRING(LOWER(`app_model`.`date`), 1, 4) 
AS `date3` FROM `app_model` ORDER BY `app_model`.`created` ASC