我有问题记录存储在数据库中。有问题的一栏是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/

10-11 22:16