Shulou(Shulou.com)05/31 Report--

This article is about how to use the date processing function in mysql, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

First create a table for the experiment.

Drop table if exists tactile students create table t_student (id int primary key auto_increment, name varchar (20) not null comment 'name', birthday date comment 'birthday') Engine=InnoDB default charset utf8;insert into t_student values (null,'tom','1992-02-03'); insert into t_student values (null,'jerry','1993-02-06'); insert into t_student values (null,'hank','1993-03-05') Insert into t_student values (null,'xiaoming',now ())

Where the date type is the type that records the exact date of the mysql

Now () function

Get the current time

Year (), month (), dayofmonth ()

The above three functions extract the year, month and day from a date or time, respectively.

For example, if you want to get a student whose birthday is February

Select * from t_student where month (birthday) = 2

Monthname () function

Output English words for each month

Select monthname (birthday) from t_student

Timestampdiff () function

Compare the difference between two days

Example: the age of the student

Select timestampdiff (year,birthday, now ()) as age from t_student

The first argument to the timestampdiff function is the unit of the result: year (year) month (month), day (day), and so on.

To_days ()

Convert date to number of days

Calculating the number of days of two times is the same as timestampdiff (day,arg1,arg2).

Query students whose birthdays are less than 60 of the current date

Select * from t_student where (to_days (now ())-to_days (birthday))

Select date_sub ('1970-1-1 year, interval 10 years); # 1970 minus 10 years

The above is how to use the date processing function in mysql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.