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

Solution to 700% CPU usage of Linux process that cannot be killed

Table of contents 1. Problem Discovery 2. View de...

js dynamically implements table addition and deletion operations

This article example shares the specific code for...

JavaScript to achieve progress bar effect

This article example shares the specific code of ...

Detailed explanation of JavaScript clipboard usage

(1) Introduction: clipboard.js is a lightweight J...

Prometheus monitors MySQL using grafana display

Table of contents Prometheus monitors MySQL throu...

Detailed explanation of Linux rpm and yum commands and usage

RPM package management A packaging and installati...

Web front-end skills summary (personal practical experience)

1. Today, when I was making a page, I encountered ...

Native JS to implement sharing sidebar

This article shares a sharing sidebar implemented...

Flex layout makes adaptive pages (syntax and examples)

Introduction to Flex Layout Flex in English means...

Linux uses lsof command to check file opening status

Preface We all know that in Linux, "everythi...

Implementation of ssh non-secret communication in linux

What is ssh Administrators can log in remotely to...

Implementation of iview permission management

Table of contents iview-admin2.0 built-in permiss...

Solution to 1449 and 1045 exceptions when connecting to MySQL

Solution to 1449 and 1045 exceptions when connect...

Detailed tutorial on installing PHP and Nginx on Centos7

As the application of centos on the server side b...