mysql 如何获得昨天插入的记录?

表中时间字段类型为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();
结果正确

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus