conversion of datetime Field to string in django queryset.values_list()

Question

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 this qs I am not getting activation date in string format. How can I convert this field('activation_date') in string or how to typecast it in qs?

Answer 1

I had a similar issue then I solved this in the following way:

from django.db.models.functions import Substr

list(  Model.objects.values('when_date').annotate(date= Substr('when_date',1,10), total=Sum('amount')) )

(Django 4.1, python 3.10)

Answer 2

If you are doing this once, refer to Yannic Hamann's answer. However if you find yourself converting to str from the database a lot, you can define the Func as a class to avoid having to type output_field and function a bunch.

class DateToChar(models.Func):
    """
    Custom Func expression to convert datetimes to str's in database query

    Params for initializer
    ------
    expression_1
        expression resulting in a date: ex: F('date')
    expression_2
        Format string as an expression: Value('YYYY-MM-DD')
    """
    arity = 2
    function = 'to_char'
    output_field = models.CharField()

Note that function will change depending on the database backend. This is written for Postgres.

This can be used like

qs = qs.annotate(date_str=DateToChar(F('date'), Value('YYYY-MM-DD'))

or with any expression that results in a date/datetime/Integer/Float/Decimal field (See Postgres to_char. Varies by database) and an expression resulting in a CharField or TextField.

See Func documentation for more information

Answer 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.models import 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_char date 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.

Answer 4

Very surprised to see that no one suggested the cast to a simple TextField (note, I'm using Postgres so I can't confirm for other RDBMSes):

from django.db.models.functions import Cast
from django.db.models import TextField

queryset = FooBarModel.objects.values(my_datetime=Cast('some_datetime_field', TextField()))
foo_bar = queryset.first()
foo_bar['my_datetime']
>>> u'2019-10-03 17:59:37.979578+00'

It similarly also works fine for nested fields:

queryset = FooBarModel.objects.values(Cast('baz__some_datetime_field', TextField()))

Alternatively, a custom Func can also be used (also specific to Postgres here, but can be modified for any other RDBMS):

class FullDateTimeCast(Func):
   """
   Coerce an expression to a new field type.
   """
   function = 'TO_CHAR'
   template = '%(function)s(%(expressions)s, \'FMDay, Month DD, YYYY at HH12:MI:SS AM\')'

queryset = FooBarModel.objects.values(my_datetime=FullDateTimeCast('some_datetime_field', TextField()))
foo_bar = queryset.first()
foo_bar['my_datetime']
>>> u' Thursday, October 03, 2019 at 17:59:37 PM'

Answer 5

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'

Answer 6

I did it this way

.annotate(date_str=ExpressionWrapper(
            Func(F('date'), Value('%d/%m/%Y %H:%i'), function='DATE_FORMAT'), output_field=CharField()
        ))

Answer 7

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'

Answer 8

You can also convert the date in queryset to string using map function. Example:

qs = MyModel.objects.filter(name='me').values_list('activation_date', flat=True)
data = map(str, qs)

Answer 9

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

Answer 10

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.