Mysql query data from a year ago a week ago yesterday _mysql

MySQL data from a year ago a week ago yesterday the main use of date_sub,

Refer to the following

Copy Code code 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 in the form of a Unix timestamp, you need to change to a uniform format for time comparisons:

Date_sub () Returns the formatted time: 2014-05-17

Need to convert Unix_timestamp () to the Unix timestamp form comparison:

Copy Code code 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 more information, see MySQL time function: date_sub, Date_add, Unix_timestamp and other functions

A variety of SQL statements to get data for the day, week, and January time in MySQL

Today, I took the time to organize a MySQL day, weeks, months of time data on the various types of SQL statements, part of the collection of data, all hand-organized, their own learning at the same time, share to everyone, and first by default to create a table, insert 2 data, easy to test some of the data, Some of the nouns or functions are explained. Straight into the subject!

To create a table:

Copy Code code as follows:

CREATE table if not exists t
(
ID int,
Addtime datetime default ' 0000-00-00 00:00:00′
)

Add two initial data:

Insert T VALUES (1, ' 2012-07-12 21:00:00′);
Insert T VALUES (2, ' 2012-07-22 21:00:00′);

Data inserted on or during the day:

1. Traditional comparison judgment: SELECT * from ' t ' WHERE date_format (addtime, '%y-%m-%d ') = Date_format (now (), '%y-%m-%d '));
2.1th Shorthand: SELECT * from ' t ' WHERE addtime >= date_format (now (), '%y-%m-%d ');
3. Use function to judge: SELECT * from ' t ' WHERE DATEDIFF (Addtime,now ()) =0;//recommend this method
4. Use timestamp to judge: SELECT * from ' t ' WHERE addtime BETWEEN (Unix_timestamp (now () -86440)) and now ();
Note: The MySQL function that returns the day time has Curdate (), Current_date (), Current_date, now (), where now () gets the date format of 0000-00-00 00:00:00 time; Curdate (), Current_date (), current_date is to obtain the date format for 0000-00-00 time, so the return time is not seconds;

Second, the month of the relevant data writing:

1. Today is the first day of the month: SELECT DayOfMonth (now ());
2. Get the first day of the month: SELECT data_sub (Now (), INTERVAL DayOfMonth (now)) –1 days;
Date arithmetic function, sentence pattern: date_add (Date,interval expr type) and date_sub (Date,interval expr type)
Date is a datetime or date value that can be used as the starting time, and expr is an expression that specifies the number of time intervals that are incremented or decreased, and can be a string-if negative, add a "-" symbol to the expression. Type is a keyword that represents the way in which expressions are interpreted, usually in years (year), months (month), Days (day), Weeks (week), and so on.
Interval's user description:

1, when the function is used, that is, INTERVAL (), for comparison functions, such as: INTERVAL (10,1,3,5,7); The result was 4;
Principle: 10 to be compared to the number, the following 1,3,5,7 for comparison, the following four followed by 10, to see how many fewer than 10 in the following number group, then return the number. The premise is that the following group of digits is arranged from small to large, otherwise the result 0 is returned.
2, when the keyword used, expressed as set time interval, commonly used in Date_add () and date_sub () function, such as: INTERVAL 1 day, interpreted as setting the time interval to 1 days.

To figure out some of the important date operations above, here's a mixed date operation.
3. First day of the last month: SELECT date_sub (Now (), INTERVAL DayOfMonth (now ())-1 days), INTERVAL 1 MONTH); is not at a glance!

Third, the week of the relevant data writing:

1. Get today is the day of the week or day of the week: SELECT Weekday (now ()); Returns the number: 0 for Monday, 6 for Sunday
2. Get the first day of the week: Select Date_sub (now (), INTERVAL Weekday (now)), or select Date_add (now (), Interval-weekday (now)) ; or SELECT curdate () –weekday (Curdate ());
3, write another last week's first day to the present data: (Table T as datasheet)
SELECT * from ' t ' WHERE addtime >= date_format (Date_sub (now (), INTERVAL Weekday (today ()) day), INTERVAL 1 WEEK), '%y-%m-%d '); Is it a bit of a feeling?

Note: If you are a timestamp-saved time field, use the From_unixtime () function to convert to a time format, such as: From_unixtime ($timestamp)

MySQL convert date to time stamp

The first three points are all about converting the date to the corresponding time constant, without mentioning the time stamp and time conversion, which is written here,

1, the time stamp turn the date, the method is select From_unixtime (1336542121);
2, date-turn time stamp, the method is: Select Unix_timestamp (' 2013-04-08′);

Combined with the previous 3 points, the time stamp is more flexible to use! Last Updated on: 2013.04.08