How to make a selection for the current month, current week, last week and last month?

In the orders table I have entered the data in the date column - 1413118559.8952 (for example), there are many such records. :)

Running around the Internet, I made a request:

Select SUM(price) FROM orders WHERE MONTH(`date`) = MONTH(NOW());

then this:

 Select SUM(price) FROM orders WHERE date > LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND date < DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY);

Requests work, but give out zero result, I need the help.

In what form the data is recorded in the database in the "date" column is unknown, since the code is encrypted, and I need to make a count of income. :)

  • The date is stored in Unix format? - Bastiane
  • I don’t know this, so what code of the request for adding there is encrypted ... The only thing I can say is: date varchar (255) utf8_general_ci - DarkAngel232
  • From the point of view of speed, it is best to calculate the start and end dates of the period and pass them to the query (query builder) and use a simple date comparison, without using functions. - AppLend
  • This is understandable, but at the beginning I simply need to find out how to derive and get the “right and necessary” result for me in general. For now - Null ... - DarkAngel232
  • Indeed yes, it works. :) Thank you so much Bastian. ^ _ ^ - DarkAngel232

1 answer 1

In theory, it should be something like this:

 select count(*) from `orders` where `date` > unix_timestamp(date_sub(current_date, interval 1 month))