MySQL gets a variety of SQL statements for day, week, January time data

Today took time to collate a MySQL in the days, weeks, months of time data related to the SQL statement of the various writing, part of the collection of information, all hand-finishing, their own learning at the same time, share to everyone, and first create a table by default, insert 2 data, easy to test part of the data, Some of these nouns or functions are explained. Straight-in topics

To create a table:

Create Table if  not exists T (   int,   datetimedefault'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 the same day or 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 ' 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 time of day has curdate (), Current_date (), Current_date, now (), where now () gets the date format of 0000-00-00 00:00:00; Curdate (), Current_date (), Current_date is the time to get the date format of 0000-00-00, so the return time is no minute or seconds;

Second, the relevant data of the month to be worded:

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, which can be used as the starting time, and expr is an expression that specifies the number of time intervals to increase or decrease, which can be a string – if negative, a "-" symbol before the expression. Type is a keyword that represents how an expression is interpreted, usually in years (year), months (month), Days (day), Weeks (week), and so on.
User description for interval:

1, when the function is used, that is INTERVAL (), for the comparison function, such as: INTERVAL (10,1,3,5,7); The result is 4;
Principle: 10 is the comparison number, the back 1,3,5,7 is the comparison number, the latter four is compared with 10 in turn, to see how many of the following number group is less than 10, then returns its number. The premise is that the subsequent number groups are arranged from small to large, otherwise the result 0 is returned.
2, when the keyword is used, expressed as a set time interval, commonly used in the Date_add () and date_sub () function, such as: INTERVAL 1 day, interpreted as the interval is set to 1 days.

Figure out some of the important date arithmetic functions above, and then a mixed date operation.
3. First day of last month: SELECT date_sub (Date_sub (now (), INTERVAL DayOfMonth (Now ()) – 1 day), INTERVAL 1 month); is not at a glance!

Third, when the relevant data of week:

1. Get today is the day of the week or day of the week: SELECT WEEKDAY (now ()); Returns the number: 0 is Monday, 6 is Sunday
2. Get the first day of the week Date: Select Date_sub (now (), INTERVAL WEEKDAY (now)), or select Date_add (now (), Interval-weekday (now ()) days) ; or SELECT curdate () –weekday (Curdate ());
3. Write a date from the first day of last week to the present data: (Table t as data table)
SELECT * from ' t ' WHERE addtime >= date_format (Date_sub (Date_sub (now (), INTERVAL WEEKDAY (now ()) day), INTERVAL 1 WEEK), '%y-%m-%d '); Do you feel something?

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

Iv. convert date to timestamp in MySQL

The first three points of the method, is to change the date to the corresponding time constant, and did not mention the time stamp and time conversion, here to write it,

1, time stamp to date, method is select From_unixtime (1336542121);
2, date to timestamp, 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

MySQL query data from a year ago a month ago a week before yesterday

MySQL yesterday a week ago a year ago the data here mainly used in the date_sub,

Refer to the following

SELECT *  fromyh_contentwhereInputtime>Date_sub (Curdate (), INTERVAL1  Day)whereInputtime>Date_sub (Curdate (), INTERVAL1WEEK)whereInputtime>Date_sub (Curdate (), INTERVAL1 MONTH)whereInputtime>Date_sub (Curdate (), INTERVAL1  Year)

Note: If the time in the database is stored in the form of a Unix timestamp, the time contrast needs to be changed to a uniform format:

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

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

whereInputtime>Unix_timestamp (Date_sub (Curdate (), INTERVAL1  Day))whereInputtime>Unix_timestamp (Date_sub (Curdate (), INTERVAL1WEEK))whereInputtime>Unix_timestamp (Date_sub (Curdate (), INTERVAL1 MONTH))whereInputtime>Unix_timestamp (Date_sub (Curdate (), INTERVAL1  Year))

See MySQL time function for details: usage of functions such as Date_sub, Date_add, Unix_timestamp, etc.

Transferred from: http://www.jb51.net/article/50505.htm

MySQL gets a variety of SQL statements for day, week, January time data