MySQL time difference functions of TIMESTAMPDIFF DATEDIFF date conversion calculation functions of date_add day date_format str_to_date

1. Time difference function (TIMESTAMPDIFF, DATEDIFF)

It is necessary to calculate the time difference with MySQL, and record the experimental results with TIMESTAMPDIFF and DATEDIFF for 1 time

--0
select datediff(now(), now());

--2
select datediff('2015-04-22 23:59:00', '2015-04-20 00:00:00');

--2
select datediff('2015-04-22 00:00:00', '2015-04-20 23:59:00');

--1
select TIMESTAMPDIFF(DAY, '2015-04-20 23:59:00', '2015-04-22 00:00:00');

--2
select TIMESTAMPDIFF(DAY, '2015-04-20 00:00:00', '2015-04-22 00:00:00');

--2
select TIMESTAMPDIFF(DAY, '2015-04-20 00:00:00', '2015-04-22 12:00:00');

--2
select TIMESTAMPDIFF(DAY, '2015-04-20 00:00:00', '2015-04-22 23:59:00');

--71
select TIMESTAMPDIFF(HOUR, '2015-04-20 00:00:00', '2015-04-22 23:00:00');

--4260
select TIMESTAMPDIFF(MINUTE, '2015-04-20 00:00:00', '2015-04-22 23:00:00');

For a description of TIMESTAMPDIFF, see http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html # function_timestampdiff

To know in what other units TIMESTAMPDIFF can return data, please refer to the description of TIMESTAMPADD: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html # function_timestampadd

2. Date conversion calculation functions (date_add, day, date_format, str_to_date)

--  Convert date to string to calculate the first day of the month 1 Day, the first day of next month 1 Days 
select curdate() as ' Current date ', 
DATE_FORMAT(curdate(), '%Y-%m') as ' Current month ', 
str_to_date(concat(DATE_FORMAT(curdate(), '%Y-%m'), '-01'), '%Y-%m-%d') as ' The first of the current month 1 Days ', 
date_add(str_to_date(concat(DATE_FORMAT(curdate(), '%Y-%m'), '-01'), '%Y-%m-%d'), interval 1 month) as ' Next month's first 1 Days ';

--  The end of the current month 1 Days 
select last_day(curdate());

--  Next month 1 Days 
select date_add(last_day(curdate()), interval 1 day);

--  What day is the day of the month 
select day(curdate());

--  The first day of the month 1 Days 
select date_add(curdate(), interval 1-(day(curdate())) day);