Create date condition in UNIX format Timestamp - Django

Asked

Viewed 247 times

0

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

How do I make one 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 don’t know how to convert the date using the Django Database API. I have it below 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 date field currently at the base is bigint. I can’t change it in the bank.

I need this consultation above in the molds 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

  • In this example query of yours, would you like to add the year as well as condition? For example: timecreated__month = now.Month, timecreated__year = now.year.

  • I would like to. The problem is that it does not work because of the type of field.

  • Using raw_sql, would that be something? cursor = Connection.cursor() cursor.execute('SELECT cast(EXTRACT(year FROM column) as bigint) as YEARS ' 'FROM despesa_tbl WHERE cd_usuario = %s ' 'GROUP BY EXTRACT(year FROM column) ' 'ORDER BY EXTRACT(year FROM column) DESC ', [request.user.id])

  • But there’s no way to do it using ORM?

  • I don’t know a way; when I needed to do this I go to raw_sql. I will study about it and if I get any solution I come back and comment.

1 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 assemble your Queryset using the argument range of the method filter, 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 why the methods get, filter and exclude of the Django models accept several arguments, which would be, say, the "Where" of SQL.

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

Worth taking a look at this document:

https://docs.djangoproject.com/en/2.0/ref/models/querysets/#field-lookups

And in this too:

https://docs.djangoproject.com/en/2.0/ref/models/querysets/#range

The example quoted above was done with Python 3.6 and Django 2.0

I hope I’ve helped

  • I need to compare only the month and year of the date that is in the bank. Your Model is correct. But since the field is Bigint, how do I extract from it only the month and year in comparison? Inside the filter

  • If I understood, you want to extract all the records from the Log model, and then compare the year and month of each, would that be it? If this is not so, and you want to extract only a specific Year/Month, I believe that the solution presented solves, you just need to create a "datetime" object with the date you want instead of the "now" used above and use the range. Anyway, if I misunderstood and want to try to explain otherwise, I am available. Hug

  • No, bro! No... I want my POST example.

  • I get it. I don’t know a way

Browser other questions tagged python django

You are not signed in. Login or sign up in order to post.