Mysql queries the data of the year before January 1, January, a week before yesterday.

Mysql data from the previous month, January, a week ago, mainly used DATE_SUB,

Refer to the following

Copy codeThe Code is as follows:
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:

Copy codeThe Code is as follows:
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:

Copy codeThe Code is as follows:
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 ()-1 DAY );
Date calculation function, sentence: date_add (date, INTERVAL expr type) and date_sub (date, INTERVAL expr type)
Date is a datetime or date value, which can be treated as the start time. expr is an expression used to specify the number of time intervals for increase or decrease. It can be a string-if it is a negative value, add a "-" symbol before the expression. Type is the keyword, which indicates the expression to be interpreted. Generally, year, month, day, week, and so on are used.
INTERVAL user description:

1. When using a function, INTERVAL () is a comparison function, for example, INTERVAL (10, 1, 3, 5, 7). The result is 4;
Principle: 10 is the compare number, followed by 1, 3, 5, and 7 is the compare number. Compare the following four with 10 in sequence. If the number of The number groups is less than 10, the number is returned. The premise is that the numbers are arranged in ascending order. Otherwise, 0 is returned.
2. When the keyword is used, it is used to set the time INTERVAL. It is often used in the functions of date_add () and date_sub (), for example, INTERVAL 1 DAY, it is interpreted as setting the interval to one day.

After figuring out the preceding several important date calculation functions, we will use a hybrid date calculation.
3. Last MONTH's first DAY: SELECT DATE_SUB (NOW (), interval dayofmonth (NOW ()-1 DAY), INTERVAL 1 MONTH); is it clear at a glance!

Iii. Writing related data for the week:

1. Obtain the day of the week or day of the week: select weekday (now (); return the number: 0 indicates Monday, and 6 indicates Sunday.
2. Obtain the first day of the week: SELECT DATE_SUB (now (), interval weekday (now () day); or SELECT DATE_ADD (now (), INTERVAL-WEEKDAY (now () day); or select curdate ()-WEEKDAY (CURDATE ());
3. Write another data from the first day of last week to the present: (Use table t as the data table)
SELECT * FROM 't'where addTime> = date_format (date_sub (now (), interval weekday (NOW () DAY), INTERVAL 1 WEEK ), '% Y-% m-% D'); does it feel a little bit!

Note: If you save the time field as a timestamp, use the from_unixtime () function to convert it to the time format, for example, from_unixtime ($ timestamp)

4. convert a date into a timestamp in Mysql

The first three methods are about converting a date into a corresponding time constant, and the time stamp and time conversion are not mentioned. Write it here,

1. The time stamp is converted to the date by selecting from_unixtime (1336542121 );
2. date to timestamp by selecting unix_timestamp ('2017-04-08 ′);

In combination with the previous three points, the timestamp can be used more flexibly! Last updated on: 2013.04.08