pyDAL - query records newer than a certain date

Question

I have the following query working in pure SQL on SQLite but do not know how to convert it to pyDAL:

SELECT * FROM buy WHERE date('now','-2 days') < timestamp;

The buy table schema is:

CREATE TABLE "buy"(
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "order_id" CHAR(512),
    "market" CHAR(512),
    "purchase_price" DOUBLE,
    "selling_price" DOUBLE,
    "amount" DOUBLE
, "timestamp" TIMESTAMP, "config_file" CHAR(512));

Answer 1

Instead of using the SQLite date function, you can create the comparison date in Python:

import datetime
cutoff_time = datetime.datetime.now() - datetime.timedelta(2)
rows = db(db.buy.timestamp > cutoff_time).select()

Alternatively, you can also pass a raw SQL string as the query:

rows = db('buy.timestamp > date("now", "-2 days")').select(db.buy.ALL)

Note, in this case, because the query within db() is simply a string, the DAL will not know which table is being selected, so it is necessary to explicitly specify the fields in the .select() (alternatively, you could add a dummy query that selects all records, such as (db.buy.id != None)).