Mysql时间函数汇总

创建表格并插入数据

create table timefunc(
str_time varchar(20),
date_time datetime);

insert into timefunc values('2020-09-28', '2020/09/28');
insert into timefunc values('2020-09-28 10:10:10', '2020/09/28 10:10:10');
insert into timefunc values('28-09-2020 10:10:10', '2020_09_28');
insert into timefunc values('09-28-2020', '2020~09~28');
insert into timefunc VALUES('1997-10-24', '1997-10-24');
insert into timefunc values('10:10:10 24-10-1997', '1997-10-24 10:10:10');
/*insert into timefunc values('28-09-2020', '28-09-2020');
ERROR 1292 (22007): Incorrect datetime value: '28-09-2020' for column 'date_time' at row 1*/

alter table timefunc add column num tinyint primary key auto_increment first;

select * from timefuc;

mysql> select * from timefunc;
+-----+---------------------+---------------------+
| num | str_time            | date_time           |
+-----+---------------------+---------------------+
|   1 | 2020-09-28          | 2020-09-28 00:00:00 |
|   2 | 2020-09-28 10:10:10 | 2020-09-28 10:10:10 |
|   3 | 28-09-2020 10:10:10 | 2020-09-28 00:00:00 |
|   4 | 09-28-2020          | 2020-09-28 00:00:00 |
|   5 | 1997-10-24          | 1997-10-24 00:00:00 |
|   6 | 10:10:10 24-10-1997 | 1997-10-24 10:10:10 |
+-----+---------------------+---------------------+
6 rows in set (0.00 sec)

Mysql日期时间格式

格式	含义
%Y	4位数年份
%y	2位数年份
%M	英文月名（January,February, …,December）
%b	英文缩写月名（Jan,Feb, …,Dec）
%m	月份,01,02,…,12
%c	月份,1,2,…, 12
%e	数字表示月中天数（1,2, …,31）
%D	英文后缀表示月中天数（1st,2nd,3rd …）
%W	一周中每一天的名称（Sunday,Monday, …,Saturday）
%w	以数字形式标识周（0=Sunday,1=Monday, …,6=Saturday）
%U	数字表示周数，星期天为周中第一天
%u	数字表示周数，星期一为周中第一天
%d	两位数字表示月中天数（01,02, …,31）
%H	24小时制
%h	12小时制
%i	分钟
%S	秒
%s	秒
%r	时间，格式为12小时(hh:mm:ss [A/P]M)
%T	时间，格式为24小时 (hh:mm:ss)

字符串转日期

select str_to_date(str_time, '%d-%m-%Y') from timefunc where num=3;
+-----------------------------------+
| str_to_date(str_time, '%d-%m-%Y') |
+-----------------------------------+
| 2020-09-28                        |
+-----------------------------------+
select str_to_date(str_time, '%H:%i:%s %d-%m-%Y') from timefunc where num=6;
+--------------------------------------------+
| str_to_date(str_time, '%H:%i:%s %d-%m-%Y') |
+--------------------------------------------+
| 1997-10-24 10:10:10                        |
+--------------------------------------------+

日期转字符串

select date_format(date_time, '%Y/%m/%d') from timefunc;
+------------------------------------+
| date_format(date_time, '%Y/%m/%d') |
+------------------------------------+
| 2020/09/28                         |
| 2020/09/28                         |
| 2020/09/28                         |
| 2020/09/28                         |
| 1997/10/24                         |
| 1997/10/24                         |
+------------------------------------+
select date_format('79-10-24 10:10:10', '%y')
-- 79
select date_format('79-10-24 10:10:10', '%Y')
-- 1979
select date_format('79-10-24 10:10:10', '%M')
-- October

日期时间计算函数

• date_add(string startdate, int days)
• datediff(string enddate, string startdate)

set @dt = now();

select date_add(@dt, interval 1 day); -- add 1 day
select date_add(@dt, interval 1 hour); -- add 1 hour
select date_add(@dt, interval 1 minute); -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);

select date_add(@dt, interval -1 day); -- sub 1 day
select date_add('2020-10-10', interval 1 hour) -- 2020-10-10 01:00:00

mysql> set @dt = '2008-08-09 12:12:33';


mysql> select date_add(@dt, interval '01:15:30' hour_second);

+------------------------------------------------+
| date_add(@dt, interval '01:15:30' hour_second) |
+------------------------------------------------+
| 2008-08-09 13:28:03 |
+------------------------------------------------+

mysql> select date_add(@dt, interval '1 01:15:30' day_second);

+-------------------------------------------------+
| date_add(@dt, interval '1 01:15:30' day_second) |
+-------------------------------------------------+
| 2008-08-10 13:28:03 |
+-------------------------------------------------+

• datediff(date1,date2), timediff(time1,time2) 1 - 2

select datediff('2008-08-08', '2008-08-01'); -- 7
select datediff('2008-08-01', '2008-08-08'); -- -7

select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
select timediff('08:08:08', '00:00:00'); -- 08:08:08

• timestamp(time1, time2)
  -类似date_add()，区别在于会自动识别time2之后加到time1上去
• timestampadd(unit,interval,datetime_expr),
• timestampdiff(unit,datetime_expr1,datetime_expr2) 2 - 1

select timestamp('2008-08-08'); -- 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01

select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00
select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00

-- MySQL timestampadd() 函数类似于 date_add()。
select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1
select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12

select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7