表中时间字段类型为TIMESTAMP 记录该条记录是何时插入的。
我现在想用固定的sql查询昨日插入的记录。以今天为例2005.10.21
那么我要的记录为2005-10-20 00:00:00 至 2005-10-20 23:59:59的记录
包含边界值.
大家看看 :)
---------------------------------------------------------------
修正:
SELECT * FROM yourTable WHERE TIMESTAMP_COLUMN >= DATE_SUB(CURDATE(), INTERVAL 1 DAY ) AND TIMESTAMP_COLUMN < CURDATE();
测试:
drop table if EXISTS timetest;
CREATE TABLE timetest
(id
int(11) NOT NULL auto_increment,inserttime
timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (id
)
) ;
INSERT INTO timetest
(id
, inserttime
) VALUES
(1,'2005-10-21 10:27:30');
INSERT INTO timetest
(id
, inserttime
) VALUES
(2,'2005-10-21 10:27:51');
INSERT INTO timetest
(id
, inserttime
) VALUES
(3,'2005-10-20 00:00:00');
INSERT INTO timetest
(id
, inserttime
) VALUES
(4,'2005-10-20 23:59:59');
SELECT * FROM timetest
WHERE inserttime >= DATE_SUB(CURDATE(), INTERVAL 1 DAY )
AND inserttime < CURDATE();
结果正确