MySQL date processing function example analysis

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.

You may also be interested in:
  • MySQL calculates the number of days, months, and years between two dates
  • Solve the problem when setting the date to 0000-00-00 00:00:00 in MySQL 8.0.13
  • Detailed explanation of the method of comparing dates in MySQL
  • Common date comparison and calculation functions in MySQL
  • MySQL date functions and date conversion and formatting functions
  • mysql gets yesterday's date, today's date, tomorrow's date, and the time of the previous hour and the next hour
  • Detailed explanation of MySQL date string timestamp conversion
  • Detailed explanation of mysql to get the current date and format

<<:  Solution to the problem of IP loss caused by copying centos8 virtual machine under VMWARE

>>:  How to Install Oracle Java 14 on Ubuntu Linux

Recommend

Linux traceroute command usage detailed explanation

Traceroute allows us to know the path that inform...

Detailed explanation of the execution process of mysql update statement

There was an article about the execution process ...

View the number of files in each subfolder of a specified folder in Linux

count script #!/bin/sh numOfArgs=$# if [ $numOfAr...

Windows DNS server exposed "worm-level" vulnerability, has existed for 17 years

Vulnerability Introduction The SigRed vulnerabili...

Tutorial on using the hyperlink tag in HTML

The various HTML documents of the website are con...

mysql-8.0.16 winx64 latest installation tutorial with pictures and text

I just started learning about databases recently....

JavaScript method to detect the type of file

Table of contents 1. How to view the binary data ...

Detailed explanation of how to use Tomcat Native to improve Tomcat IO efficiency

Table of contents Introduction How to connect to ...

Summary of some problems encountered when integrating echarts with vue.js

Preface I'm currently working on the data ana...

A brief analysis of Linux network programming functions

Table of contents 1. Create a socket 2. Bind sock...

How to find the specified content of a large file in Linux

Think big and small, then redirect. Sometimes Lin...