How do I select data between two dates using custom sql in django?

Question

views.py

st_date, end_date = week_magic(date.today())
cur = connection.cursor()
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])
row2=dictfetchall(cur)
cur.close()
data_json = json.dumps(row2, datetime.datetime)

st_date= 2016-01-18 and the end_date = 2016-01-24. While I was executing the code, the following error occured.

"list indices must be integers, not datetime.date".

How do I select data between two dates using custom sql?

Anoop · Accepted Answer · 2016-01-20 04:26:09Z

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))

answered Jan 20, 2016 at 4:26

Anoop

2,8184 gold badges21 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Oleksii M · Accepted Answer · 2016-01-20 07:35:25Z

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'.

Collectives™ on Stack Overflow

How do I select data between two dates using custom sql in django?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related