MySQL date processing function example analysis

This article mainly introduces the example analysis of MySQL date processing functions. The example code is introduced in great detail in this article, which has a certain reference value for everyone's study or work. Friends in need can refer to it.

First, create a table for the experiment

drop table if exists t_student;

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

The date type is the type that records the exact date of MySQL.

now() Function

Get the current time

year() , month(), dayofmonth()

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

For example, if you want to get students whose birthdays are in February

select * from t_student where month(birthday) = 2;

monthname() Function

Output the English word for the month

select monthname(birthday) from t_student;

timestampdiff() Function

Compare the difference between two dates

Example: The age of the student

select timestampdiff(year,birthday ,now()) as age from t_student;

The first parameter of the timestampdiff function is the unit of the calculation result: year, month, day, etc.

to_days()

Convert date to days

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

Query students whose birthdays are less than 60 days from the current date

select * from t_student where (to_days(now()) - to_days(birthday)) < 60;

date_add and date_sub

Calculate another date based on a date. date_add is for addition and date_sub is for subtraction.

select date_add('1970-1-1', interval 10 year); # 1970 plus 10 years

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

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.