Create condition with date in UNIX format Timestamp - Django

Question

Create condition with date in UNIX format Timestamp - Django

Navigation

#1 by (0 votes)

0

I have a table in the database that the date fields are of type BigInteger, in which it receives a date in the format Timestamp.

How do I make a SELECT in the table with the following conditions:

SELECT
    COUNT(log.id) AS QTD
FROM
    mdl_logstore_standard_log log
INNER JOIN mdl_user ON mdl_user.id = log.userid
AND suspended = 0
AND deleted = 0
WHERE
    MONTH (
        from_unixtime(log.timecreated)
    ) = MONTH (now())
AND YEAR (
    from_unixtime(log.timecreated)
) = YEAR (now())
AND action = 'loggedin'
AND userid > 1
GROUP BY
    MONTH (
        from_unixtime(log.timecreated)
    ),
    YEAR (
        from_unixtime(log.timecreated)
    )

I need it to be the month and year number. But I do not know how to convert the date using the Django Database API. I have this down so far:

totalAccessMonth    = Log.objects.filter(timecreated__month = now.month).all()

What I want is to convert the column in real time to a date type. The field currently in base is bigint . I can not change it in the bank.

I need this query in the form of Django.

For now I have this:

Log.objects.filter(action = 'loggedin', userid__gt = 1) \
.extra(select={'timecreated_year' : 'YEAR(FROM_UNIXTIME(timecreated))'}) \
.extra(select={'timecreated_month' : 'MONTH(FROM_UNIXTIME(timecreated))'}) \
.values_list('timecreated_month', flat = True) \
.aggregate(Count('id'))

python django

asked by anonymous 05.01.2018 / 18:25

1 answer

Importing column from GridView to TextBox Error trying to access WebService

score 0 · Answer 1

If I understood the doubt, given the following model:

from django.db import models

class Log(models.Model):
      timecreated = model.BigIntegerField()

You can mount your QuerySet using the range argument of the filter method, as follows:

import datetime
import calendar

now = datetime.datetime.now()
start_date = datetime.date.today().replace(day=1)
end_date =  datetime.date(now.year, now.month, monthrange(now.year, now.month)[1])

Log.objects.filter(createdtime__range=(start_date.strftime("%s"), end_date.strftime("%s")))

This is because the get , filter , and exclude methods of Django models accept several arguments, which would be to say, the "where" of SQL.

range is equivalent to BETWEEN of SQL, and can be used with numbers, dates, and even characters.

It's worth taking a look at this document:

link

And here too:

link

The example above was done with Python 3.6 and Django 2.0

I hope I have helped