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

Question

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?

Gordon Linoff · Accepted Answer · 2013-07-22 23:01:47Z

1

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.

edited Jul 22, 2013 at 23:01

answered Jul 22, 2013 at 2:30

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

fox Over a year ago

So the actual query that we're editing looks as follows: select distinct <field1>, <field2>, count(1) as num FROM Table group by <field1>, <field2>;. Where would we add the above query into that if we wanted to filter it by the last 60 days?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related