sqlite3，用datetim选择

Question

问题

我尝试在Python中使用WHERE部分中的datetime执行sqlite3查询。在

placedDate是datetime对象，我的代码如下。由于某些原因，查询返回今天下注的项目，而不是最后一小时内的下注。怎么了？在

    sql = r"""SELECT count(*) FROM bet WHERE placedDate>?"""
    td = timedelta( minutes=60 )
    dt = datetime.utcnow()-td
    with closing( conn.cursor() ) as cur:
        cur.execute( sql, ( dt, ) )
        print cur.fetchone()[0]

我也试过了

^{pr2}$

placedDate的一个例子是

 'placedDate': u'2015-02-06T01:20:37.000Z', 

表定义

使用

    meta = conn.execute("PRAGMA table_info('bet')")
    print( 'bet table info:')
    for r in meta:
        print r

我明白了

(0, u'id', u'INTEGER', 0, None, 1)
(1, u'marketId', u'varchar', 0, None, 0)
(2, u'sizeMatched', u'decimal(10,2)', 0, None, 0)
(3, u'orderType', u'varchar', 0, None, 0)
(4, u'selectionId', u'int', 0, None, 0)
(5, u'price', u'decimal(5,2)', 0, None, 0)
(6, u'persistenceType', u'varchar', 0, None, 0)
(7, u'size', u'decimal(10,2)', 0, None, 0)
(8, u'placedDate', u'datetime', 0, None, 0)
(9, u'averagePriceMatched', u'decimal(10,2)', 0, None, 0)
(10, u'side', u'varchar', 0, None, 0)
(11, u'description', u'varchar', 0, None, 0)

Answer 1

这对我有用：

 import sqlite3
 from datetime import datetime, timedelta

 conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)


 c = conn.cursor()

 c.execute("""
 CREATE TABLE test (
    foo varchar,
    ts datetime
 );""")

 rowcount = c.execute("SELECT COUNT(*) FROM test").fetchone()[0]
 assert 0 == rowcount, rowcount

 dt = timedelta(minutes=5)

 now = datetime.now()

 then = now

 for i in xrange(100):
     then -= dt
     c.execute("INSERT INTO test VALUES(?, ?)", (str(i), then))

 rowcount = c.execute("SELECT COUNT(*) FROM test").fetchone()[0]
 assert 100 == rowcount, rowcount


 hour_ago = now - timedelta(minutes=60)
 rowcount = c.execute(
     "SELECT COUNT(*) FROM test WHERE ts >= ?",
     (hour_ago,) ).fetchone()[0]
 assert 12 == rowcount, rowcount

如果对你有用，我怀疑数据与你的预期不同。在