Bili Bili - 1 month ago 6
MySQL Question

Select Orders of Last Month in MySQL

I have to write a query that selects Last month orders from a table Order( id , ......., order_date).

Assume that the date of today is 4th of April. Will i select orders from 1st March to 31th of March or from the 4th of March to the 4th of April ???

Edit :
The first query :

SELECT * from Order where order_date between sysdate() and sysdate - INTERVAL 1 Month


The Second Query :

SELECT * from Order where order_date between (Last_day(sysdate()) - INETRVAL 1 MONTH)
and ((Last_day(sysdate()) + INTERVAL 1 DAY) - INETRVAL 2 MONTH))


(Last_day(sysdate()) - INETRVAL 1 MONTH)
gives 30 of March

((Last_day(sysdate()) + INTERVAL 1 DAY) - INETRVAL 2 MONTH))
gives 1st of March

Answer

"Last month" typically means the whole period of the month prior to today. So if today was 4th April then last month is

 >= 1st March and < 1st April

E.g.

Select * from orders
Where order_date >= '2016-03-01' 
and order_date < '2016-04-01'

Note I recommend avoiding between for date ranges. Also note that the time of 23:59:59 is NOT the end of a day and that recent versions of mysql do support subsecond time precision.