SQLite3 selecting entries inserted within last 24 hours in Flask

user_11685324 0
Viewed 125

I am new to flask and SQLite3. I want to select the entries inserted into the table from the past 24 hours.

the entries are inserted as follows:

    now=datetime.now()
    formatted_date=now.strftime('%Y-%m-%d %H:%M:%S')
    ip=request.remote_addr
    txt=request.form['txt']
    with sql.connect("static/database.db") as con:
    cur = con.cursor()
    cur.execute('INSERT into messagetable (txt,dateandtime,ip) values (?,?,?)',(txt,formatted_date,ip))
    con.commit()

and the selection is done by:

   con = sql.connect("static/database.db")
   con.row_factory = sql.Row

   cur = con.cursor()
   cur.execute('SELECT * FROM messagetable WHERE (current_date-dateandtime<1)')
   rows = cur.fetchall();

But the final result shows all the entries in the table, and not exclusively the ones posted within the last 24 hours. What could be the problem?

flask sqlite sql python
1 Answers

I think you are looking for something like this:

cur.execute("SELECT * FROM messagetable WHERE dateandtime >= date('now', '-1 days') AND dateandtime < date('now')")
user_60849640
Related