Get year from Django DateTimeField with values ​​()

Question

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

Example:

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 years and the 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 

Summary request:

 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

Here is what I did. Gocht's solution looks like it should work, but I had problems (Django 1.10, SQLite) when Django was processing the results. I did not want to use RawSQL , but this is the only thing I need 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, not a string; remove CAST(... AS integer) if you want a string.

You will need to change table_name to what will be in your model database table.

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

Answer 2

I found this question very interesting. So I played with the console for a while.

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

 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 indicate your year as a string.

Note: this will work if in _date you get something like this: u'2015-08-24 09:45:16' , if you get another row, you can change the indices in Substr('_date', 1, 4) . You can see which string you get in _date by adding it to .values('_date', 'publish_year') .

Hope this helps.

Additionally:

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 the final result for me.

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