MySQL从上个月选择所有行,直到(now() - 1个月),用于比较 [英] MySQL select all rows from last month until (now() - 1 month), for comparative purposes
问题描述
我需要一些帮助,编写一个MySQL查询来显示我上个月的行,但不是整个月,直到现在(),而是前一个月的时间,直到同一天,小时和分钟。所以例如,如果今天是5月5日下午5点,我需要从午夜12:00到4月1日下午4点到5点25分(从当然也是同一年)。
谢谢!
你可以得到一个月的第一个,通过计算前一个月的最后一天,并添加一天。这是尴尬的,但我认为它比将日期格式化为字符串更好,并用于计算。
select
*
从
yourtable t
其中
/ *大于或等于上个月的开始* /
t.date> = DATE_ADD(LAST_DAY(DATE_SUB (NOW(),INTERVAL 2 MONTH)),INTERVAL 1 DAY)和
/ *小于或等于一个月前* /
t.date< = DATE_SUB(NOW(),INTERVAL 1 MONTH )
I need some help writing a MySQL query to show me rows from last month, but not the whole month, only up and until the same day, hour and minute as it is now(), but 1 month before.
So for example, if today is 5/19 at 5:25pm I need to select rows from midnight 12:00am 4/1 to 5:25pm of 4/19 (from the same year too of course).
Thanks!
You can get the first of the month, by calculating the last_day of the month before and add one day. It is awkward, but I think it is better than formatting a date as string and use that for calculation.
select
*
from
yourtable t
where
/* Greater or equal to the start of last month */
t.date >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY) and
/* Smaller or equal than one month ago */
t.date <= DATE_SUB(NOW(), INTERVAL 1 MONTH)
这篇关于MySQL从上个月选择所有行,直到(now() - 1个月),用于比较的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!