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) |
字符串转日期
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
上一篇: css的三个特性 背景透明设置
下一篇: 验证手机号码的JS方法