How to use date handling function in mysql

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))

< 60; date_add 和 date_sub 根据一个日期 ，计算出另一个日期， date_add 是加上 date_sub 是减去 select date_add('1970-1-1', interval 10 year); # 1970 年 加上10年

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.