CODEWITHSUNDEEP
pythonsqlpython-2.7sqlite
fox
fox

Reputation: 16496

python: query a DATETIME field in a sqlite db for a date range

Have a sqlite db that I've created in python that has a DATETIME field:

import sqlite3
con = sqlite3.connect('some.db',detect_types=sqlite3.PARSE_DECLTYPES)
with con:
    cur = con.cursor()
    cur.execute("CREATE TABLE Table(...Date DATETIME...)")
    ...
    Date = datetime.datetime(<a format that resolves to the correct datetime object>)
    ...
    altogether = (..., Date, ...)
    cur.execute("INSERT INTO Table VALUES(...?...)", altogether)
    con.commit()

This populates correctly. I later want to be able to query this DB by datetime, and have a function to manage my queries generally:

def query_db(path, query, args=(), one=False):
    connection  = sqlite3.connect(path)
    cur = connection.execute(query, args)
    rv = [dict((cur.description[idx][0], value)
               for idx, value in enumerate(row)) for row in cur.fetchall()]
    return (rv[0] if rv else None) if one else rv

LOCAL_FOLDER = os.getcwd()
samplequery = "SELECT * FROM Table"
dbFile  = os.path.join(LOCAL_FOLDER, "some.db")
result = query_db(dbFile, samplequery)

The above would successfully produce a resultthat gave me everything in the Table.

However, how do I structure a query that would, for instance, give me all entries in the Table table of some.db that have a Date within the past 60 days?

Upvotes: 0

Views: 2882

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can do a query like this:

SELECT *
FROM Table
where date >= date('now', '-60 day');

EDIT:

Based on your actual query:

select <field1>, <field2>, count(1) as num
FROM Table
where date >= date('now', '-60 day');
group by <field1>, <field2>;

SELECT DISTINCT is unnecessary when you are using GROUP BY.

Upvotes: 1

Related Questions