Create a table by writing various SQL statements for mysql to obtain data for one day, one week, or one month:
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 ())