从MySql返回少于60天的行

Question

我有以下查询返回表中的所有行:

$query="SELECT * FROM $tbl_name ORDER BY job_id DESC";

我想限制这些结果少于60天的条目。我使用以下命令记录一个条目进入数据库的日期:

$dt=date('d M Y');

数据存储在一个名为'date'的列中。

谁能帮我修改一下我的查询?

谢谢丹

Answer 1

如果date像varchar一样存储在数据库中，那么您的查询应该是:

SELECT * 
FROM $tbl_name 
WHERE TO_DATE(date, 'dd MON yyyy') >= DATE_SUB(CURDATE(), INTERVAL 60 DAY)
ORDER BY job_id DESC

如果date以日期形式存储，则使用:

SELECT * 
FROM $tbl_name 
WHERE date >= DATE_SUB(CURDATE(), INTERVAL 60 DAY)
ORDER BY job_id DESC

Answer 2

    $query="SELECT * FROM $tbl_name WHERE DATEDIFF(CURDATE(), STR_TO_DATE(date,'%d %M %Y')) <60 ORDER BY job_id DESC"

Answer 3

SELECT * FROM $tbl_name FROM_UNIXTIME(date) >= DATE_SUB(CURDATE(), INTERVAL 60 DAY) ORDER BY job_id DESC

Answer 4

Try this(未经测试)

SELECT * FROM $tbl_name WHERE date >= DATE_SUB(CURDATE(), INTERVAL 60 DAY) ORDER BY job_id DESC

Answer 5

可以使用as

select  b.mobile_number, max(b.bill_number), max(b.created_on), c.name from MasterBill as b, MasterCustomer as c  WHERE b.created_on < NOW() - INTERVAL 60 DAY and b.mobile_number = c.mobile_number group by mobile_number;

考虑手机号码是客户主控和计费的关键绑定数据。