How to query data for one week per time in mySQL

I am using python 3.4 to query the data in mysql. I would like to query one year's worth of data from a large database so I plan to split the query to query for one week per time.

This is the query I am using to query the data.

SELECT id, event_title, event_deadline FROM job
WHERE country_id = 15
AND (event_deadline >= '2015-01-01' AND event_deadline <= '2015-01-07')

Is there an easier way to grab all the data for all 52 weeks in a year without manually listing out the dates for each week like below?

week 1: 2015-01-01 - 2015-01-07
week 2: 2015-01-08 - 2015-01-14
week 3: 2015-01-15 - 2015-01-21

Answers


You could use the MySQL WEEK() function, something like this: (Forgive me if this looks like php; I haven't done much python. Nonetheless, the principle is the same.)

query = ("SELECT id, event_title, event_deadline FROM job "
            "WHERE country_id = 15 "
              "AND WEEK(event_deadline) = %s")

for weeknum in [1..53]
    cursor.execute(query, weeknum)
    do_stuff_with(cursor)

Check the MySQL docs for details on how Mysql's WEEK() function defines a calendar week.

Hope that makes enough sense to be useful.


Need Your Help

How to strip a GET property from the URL using PHP

php

I have the following function that get's the current page URL:

SSL certificate is showing issuer[Issued by] as mydomain

ssl ssl-certificate

I was looking at few sites and their SSL certificate and I noticed that few SSL certificates shows Issuer[Issued by] as mydomain[myorganisation]