Mysql Query data from a year before July 15, January, a week before yesterday

Mysql queries the data of a year before July 15, and a year before July 15, and a week before July 15,. DATE_SUB is mainly used here,

Refer to the following

SELECT * FROM yh_content
Where inputtime> DATE_SUB (CURDATE (), INTERVAL 1 DAY)
Where inputtime> DATE_SUB (CURDATE (), INTERVAL 1 WEEK)
Where inputtime> DATE_SUB (CURDATE (), INTERVAL 1 MONTH)
Where inputtime> DATE_SUB (CURDATE (), INTERVAL 1 YEAR)

Note: If the time in the database is stored as a UNIX timestamp, you need to change the time comparison to a unified format:

DATE_SUB () returns the formatted time:

You need to convert UNIX_TIMESTAMP () to UNIX timestamp format for comparison:

Where inputtime> UNIX_TIMESTAMP (DATE_SUB (CURDATE (), INTERVAL 1 DAY ))
Where inputtime> UNIX_TIMESTAMP (DATE_SUB (CURDATE (), INTERVAL 1 WEEK ))
Where inputtime> UNIX_TIMESTAMP (DATE_SUB (CURDATE (), INTERVAL 1 MONTH ))
Where inputtime> UNIX_TIMESTAMP (DATE_SUB (CURDATE (), INTERVAL 1 YEAR ))

For details, see the usage of MySql time functions, such as DATE_SUB, DATE_ADD, and UNIX_TIMESTAMP.

How to write SQL statements for mysql to obtain data from one day, one week, or one month

Today, I took the time to sort out a variety of SQL statements related to the time data of days, weeks, and months in mysql, some of which are collected data, all of which are hand-organized and learned by myself, to share with you, create a table by default and insert two pieces of data to facilitate testing of some data. some terms or functions are explained. Go to the topic!

Create a table:

Create table if not exists t
(
Id int,
AddTime datetime default '2017-00-00 00:00:00 ′
)

Add two initial data items:

Insert t values (1, '2017-07-12 21:00:00 ′);
Insert t values (2, '2017-07-22 21:00:00 ′);

1. data inserted on the current or current day:

1. traditional comparison judgment: SELECT * FROM 't'where DATE_FORMAT (addTime, '% Y-% m-% d') = date_format (now (), '% Y-% m-% d ')");
2. abbreviation of the first point: SELECT * FROM 't'where addTime> = date_format (NOW (), '% Y-% m-% d ');
3. use the function to determine: SELECT * FROM 't'where DATEDIFF (addTime, NOW () = 0; // This method is recommended.
4. use the timestamp to determine: SELECT * FROM 't'where addTime BETWEEN (UNIX_TIMESTAMP (now ()-86440) AND now ();
Note: the mysql functions returned for the current day include CURDATE (), CURRENT_DATE (), CURRENT_DATE, and NOW () the obtained date is in the format of 0000-00-00 00:00:00; CURDATE (), CURRENT_DATE (), and CURRENT_DATE are the time for obtaining the date format of, so the returned Time is not in time, minute, and second;

II. related data writing for the current month:

1. today is the day of the month: select dayofmonth (NOW ());
2. obtain the first day of the month: SELECT DATA_SUB (NOW (), interval dayofmonth (NOW ())