请问SELECT *（全部）哪个日期从上个月的最后一天开始到下个月的第一天

use PHP and MySQL and want to use SELECT statement which date_post(datetime variable) start at the last date of last month and to date the first day of next month, help me please.

使用PHP和MySQL,并希望使用SELECT语句,date_post(datetime变量)从上个月的最后一个日期开始,到下个月的第一天,请帮助我。

Thank you in advance.

先谢谢你。

my database: 'id', 'content', 'image', 'date_post',

etc. and I try to use

等我尝试使用

$today = getdate();
$thisyear=$today['year'];
$thismon=$today['mon'];
$date_start=$thisyear.'-'.$thismon.'-01';
$date_end=$thisyear.'-'.($thismon+1).'-01';
$sql="SELECT *, DATE_FORMAT(date_post, '%d-%m-%Y') AS datepost
      FROM my_table
      WHERE date_post BETWEEN date('$date_start') 
        AND date('$date_end')
      ORDER BY date_post DESC";

2 个解决方案

#1

It makes with one query in MySQL, without any PHP:

它在MySQL中使用一个查询,没有任何PHP:

SELECT * FROM `table_name`
WHERE DATE(`date_post`) >= DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, CONCAT('%Y-%m-', DAY(LAST_DAY(CURDATE() - INTERVAL 1 MONTH))))
AND DATE(`date_post`) <= DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01');

#2

Ensuring that the query will not scan the full table but will use the index of date_post (if there is one!):

确保查询不会扫描整个表,但会使用date_post的索引(如果有的话!):

SELECT * 
FROM myTable
WHERE date_post < LAST_DAY(CURDATE())
                   + INTERVAL 2 DAY
  AND date_Post >= LAST_DAY( LAST_DAY( CURDATE()) - INTERVAL 1 MONTH )

If it is run today ( 2011-07-01 ), it will give all datetimes between 2011-06-31 00:00:00 and 2011-08-01 23:59:59.

如果今天运行(2011-07-01),它将在2011-06-31 00:00:00和2011-08-01 23:59:59之间提供所有日期时间。

#1