How to get django queryset results with formatted datetime field

Question

I've Django model which has foreign keys associated with other models. Each model is having same field names(attributes) created_at and updated_at

In every django queryset results I'll be getting datetime values.

Model.objects.all().values('created_at')

But I want to format the datetime field to "DD-MM-YYYY HH:MM:SS" and trim down the milliseconds in the django query results.

If I use "extra" and and date_trunc_sql like the following command

dt = connection.ops.date_trunc_sql('day','created_date')
objects.extra({'date':dt}).values('date')

Which works fine. But If I query like the following, its raising ambiguous statement error.

objects.extra({'date':dt}).values('date', 'x', 'y', 'z')

How to overcome this problem?

Answer 1

Got the solution.

data = list(Model.objects.extra(select={'date':"to_char(<DATABASENAME>_<TableName>.created_at, 'YYYY-MM-DD hh:mi AM')"}).values_list('date', flat='true')

It's not just tablename.attribute, it should be dbname_tablename.attribute when we have multiple databases(ambiguous)

which will result list of created_at datetime values trimmed to 'YYYY-MM-DD HH:MM' format.

Answer 2

Solved it via @Yannics answer at: https://stackoverflow.com/a/60924664/5804947

This also avoids using extra which should be "a last resort" due to Django docs.

from django.db.models import F, Func, Value, CharField

qs.annotate(
  formatted_date=Func(
    F('created_at'),
    Value('DD-MM-YYYY HH:MM:SS'),
    function='to_char',
    output_field=CharField()
  )
)

Answer 3

I don't think values() function would anything related to formatting datetime result. But why does that bother you? Can't you convert them to proper format when you try to display them? If you try to render them in the template, django has template filter date for formatting your datetime value: https://docs.djangoproject.com/en/1.9/ref/templates/builtins/#date