我有问题记录存储在数据库中。有问题的一栏是ask_date
。
存储的示例是:1549923808
。
我想选择ask_date
在当前月份内的记录。
请在SQL的WHERE
子句中如何计算?
//Select questions with the heighest or Top votes or answers this month( current month)
$SQL = "SELECT * FROM(
SELECT Q.*,
(SELECT COUNT(Q.question_id) FROM $questions_table Q ) num_of_rows,
(SELECT COUNT(v.vote_id) FROM $votes_table v WHERE Q.question_id=v.ask_id AND v.vote_type=0 ) votes_down,
(SELECT COUNT(a.answer_id) FROM $answers_table a WHERE question_id=a.ask_id ) total_answers,
(SELECT COUNT(v.vote_id) FROM $votes_table v WHERE Q.question_id=v.ask_id ) votes_up,
CONCAT(m.firstname,' ',m.lastname) author_name,
m.username u_name FROM $questions_table Q
LEFT JOIN $main_table m ON Q.user_id=m.user_id
WHERE ............
) A LEFT JOIN $votes_table V ON A.question_id=V.ask_id
GROUP BY A.question_id ORDER BY (
SELECT COUNT(V.vote_id) vote
FROM $votes_table V
WHERE V.ask_id=A.question_id
) DESC LIMIT {$maxPageRecords} OFFSET {$offset}";
最佳答案
一种简单的方法是:
where date_format(from_unixtime(ask_date), '%Y-%m') = date_format(now(), '%Y-%m')
但是,这排除了索引的使用。因此,这可能更好:
where ask_date >= unix_timestamp(date(concat_ws('-', year(now()), month(now()), 1)))) and
ask_date < unix_timestamp(date(concat_ws('-', year(now()), month(now()), 1))) + interval 1 month)
关于php - 在MySQL中检查Unix时间戳是否在当月内,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/54856238/