欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Mysql时间函数汇总

程序员文章站 2022-04-04 08:32:08
...

创建表格并插入数据

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)

Mysql时间函数汇总

字符串转日期

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                         |
+------------------------------------+

日期时间计算函数

  • date_add()
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
mysql> set @dt = '2008-08-09 12:12:33';

mysql>
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)
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
  • timestampadd(unit,interval,datetime_expr),
  • timestampdiff(unit,datetime_expr1,datetime_expr2)
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