Geek Answers Handbook

MySQL Select last month data by current_timestamp

until today, when I was working with MySQL and had to perform actions with date / time, I used int column with unix timestamp, and there were no problems, but today, after reading a few tutorials, I decided to test the timestamp column with "current_timestamp", by by default.

So, I am wondering how to select data last month in the column where the information is in the format "2012-09-07 00:23:30"? And maybe there are some tricky questions that will give me data from the beginning of this month (not the last 30 days, but from 09-01 00:00:00 to today)?

+7
source share
3 answers

This will give you the last month:

WHERE dateColumn BETWEEN SUBDATE(CURDATE(), INTERVAL 1 MONTH) AND NOW();

This is from the beginning of the month:

 WHERE dateColumn BETWEEN STR_TO_DATE('2012-09-01', '%Y-%m-%d') AND NOW();

BETWEEN is nothing special, it's just a shortcut to

 dateColumn <= ... AND dateColumn >= ....

Hmm, I assume that comparing NOW () is not really required, since all entries will be before that.

So simple:

 WHERE dateColumn >= STR_TO_DATE('2012-09-01', '%Y-%m-%d')

Dynamic start of the current month:

 WHERE dateColumn >= CURDATE() - INTERVAL DAY(CURDATE())-1 DAY

All this is to extract the day of the month from the current date, and then subtract from it a few days less.

+17
source

You should take a look at common_schema , it is easy to install and has many great tools, including the start_of_month() function, this is exactly what you are looking for:

 select * from your_table where your_column >= common_schema.start_of_month(now())
+3
source

If you have mysql timestamp, something like 2013-09-29 22:27:10 , you can do it

  select * from table WHERE MONTH(FROM_UNIXTIME(UNIX_TIMESTAMP(time)))=9;

Convert to unix, then use temporary unix functions to extract the month, in this case 9 for September.

-one
source

More articles:


All Articles