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

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 2

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.