[Fixed]-How do I select data between two dates using custom sql in django?

by

1👍

✅

Try this,

cur.execute("select myapp_deal.*,CONCAT(myapp_contacts1.first_name,myapp_contacts1.last_name) as full_name from myapp_deal LEFT JOIN myapp_contacts1 on myapp_contacts1.id = myapp_deal.contact_id where myapp_deal.closed_date BETWEEN" '%s' "and" '%s',(st_date,end_date))
👤Anoop

0👍

A have the dirty solution for PostgresQL and django 1.7:

from django.db.models import query

DATE_COMPARE_TEMPLATE = '("{0}" AT TIME ZONE \'{1}\')::date {2} \'{3}\'::date'


def offset_to_string(timezone_offset_m):
    return '{sign}{hours}:{minutes}'.format(**{
        # note invesred signs: '-' for positive, '+' for negative
        # Postgres uses this notation to calculate offset
        'sign': '-' if timezone_offset_m > 0 else '+',
        'hours': '{:0>2}'.format(abs(int(timezone_offset_m)) // 60),
        'minutes': '{:0>2}'.format(abs(int(timezone_offset_m)) % 60),
    })


class PeriodQuerySet(query.QuerySet):

    def filter_date(self, field, compare, value, timezone_offset_m=0):
        timezone = offset_to_string(timezone_offset_m)
        query = DATE_COMPARE_TEMPLATE.format(field,
                                             timezone,
                                             compare,
                                             value.isoformat())
        qs = self.extra(where=[query])
        return qs

And usage (for model, where the queryset is attached):

from django.utils import timezone
Period.objects.filter_date('start_time', '>', timezone.now().date())

One of possible pitfalls could be name collisions if query contains two tables which both have columns ‘start_time’.

👤Oleksii M

Leave a comment