CODEWITHSUNDEEP
pythondjangodatetimedjango-models
user2959723
user2959723

Reputation: 671

How to get django queryset results with formatted datetime field

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?

Upvotes: 14

Views: 23509

Answers (3)

Henhuy
Henhuy

Reputation: 1154

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()
  )
)

Upvotes: 23

user2959723
user2959723

Reputation: 671

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.

Upvotes: 4

Shang Wang
Shang Wang

Reputation: 25539

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

Upvotes: 1

Related Questions