Conversion Of Datetime Field To String In Django Queryset.values_list()

I have a queryset like: qs = MyModel.objects.filter(name='me').values_list('activation_date') here activation_date is DateTimeField in models. When I download excel sheet from thi

Solution 1:

https://docs.djangoproject.com/en/2.2/ref/models/fields/#datetimefield

A date and time, represented in Python by a datetime.datetime instance.

You can get a string representation of a DateTimeField casting it directly:

str(obj)
# obj = qs[0][0] ? or qs[0][1] ?

You'll get result like this (in this example I use datetime.datetime.now() since a DateTimeField is represented by datetime.datetime is the same behavior):

>>>now = datetime.datetime.now()>>>str(now)
'2013-06-26 00:14:26.260524'

if you want less information or formatted in other mode you can use strftime() function for format them. see:

>>> now.strftime('%Y-%m-%d %H:%M')
'2013-06-26 00:14'

Solution 2:

extra is deprecated in Django 2.0

That's why I think the best solution to get a stringified datetime is:

foo_bar = FooBarModel.objects.annotate(
    str_datetime=Cast(
        TruncSecond('some_datetime_field', DateTimeField()), CharField()
    )
).values('str_datetime').first()

The result is:

foo_bar.str_datetime:
(str)'2014-03-28 15:36:55'

---

Also I'd like to mention that you can format it as well in any way you want like:

from django.db.models import Value

foo_bar = FooBarModel.objects.annotate(
    day=Cast(ExtractDay('some_datetime_field'), CharField()),
    hour=Cast(ExtractHour('some_datetime_field'), CharField()),
    str_datetime=Concat(
        Value('Days: '), 'day', Value(' Hours: '), 'hour', 
        output_field=CharField()
    )
).values('str_datetime').first()

The result is:

foo_bar.str_datetime:(str)'Days: 28 Hours: 15'

Solution 3:

extra() is an old API that Django aims to deprecate at some point in the future. I would avoid using it.

Try the following instead:

from django.db.modelsimport F, Func, Value, CharField

qs.annotate(
  formatted_date=Func(
    F('date'),
    Value('dd.MM.yyyy hh:mm'),
    function='to_char',
    output_field=CharField()
  )
)

This works only with a database that supports the to_chardate type formatting function. Postgres provides this function by default.

• If you use a MSSQL backend you could swap to_char with FORMAT.
• For MySQL use DATE_FORMAT.
• For Oracle consult their documentation, etc.

After the queryset is evaluated this will add the annotation formatted_date to each object in the queryset that is returned.

Solution 4:

If you are using Postgres, you can do it like this (date format options here). The solution is database dependent, but it sure beats looping though a long list in Python land after your perform the query.

qs = MyModel.objects.filter(name='me')
qs = qs.extra(select={'datestr':"to_char(activation_date, 'YYYY-MM-DD HH24:MI:SS')"})
qs = qs.values_list('datestr')

I am sure MySQL has some equivalent function as Postgres's to_char, but you'll have to find that on your own as I am not a MySQL guy.

Solution 5:

qs = MyModel.objects.filter(name='me')
qs = qs.extra(select={'datestr':"DATE_FORMAT(activation_date, '%Y-%m-%d')"})
qs = qs.values_list('datestr')