mysql时间函数详解

1、 从时间中解析出年月日时间：date_format()

• 函数解析：date_format() 有两个参数，一个是date, 另一个是format；原理是将时间(date)，转化成我们想要的格式(format)进行输出
• 前提条件：date_format() 传入的date必须是时间格式

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

-- output
Dec 29 2008 11:45 PM
12-29-2008
29 Dec 08
29 Dec 2008 16:25:46.635

2、 如何将yyyymmdd格式转换成时间格式：

方法一：unix_timestamp() / from_unixtime()

• stp1：将yyyymmdd格式转换成unix时间戳
• stp2：将unix格式时间戳转换成我们想要的格式

-- unixtime
SELECT UNIX_TIMESTAMP(‘2012-06-08’)       
output：1339084800

--以下几种格式返回的结果相同：
SELECT UNIX_TIMESTAMP('20120608');
SELECT UNIX_TIMESTAMP('2012-6-8');
SELECT UNIX_TIMESTAMP('2012-06-08');
output：1339084800

-- from_unixtime
SELECT FROM_UNIXTIME( 1388776825, '%Y-%m-%d %H:%m:%s');
output：2014-01-04 03:01:25


-- 案例：
--20210305转成2021-03-05
select from_unixtime(unix_timestamp('20210305','yyyymmdd'),'yyyy-mm-dd') from table;

--2021-03-05转成20210305
select from_unixtime(unix_timestamp('2021-03-05','yyyy-mm-dd'),'yyyymmdd') from table;

方法二：substr + concat

• substr：从string中提取指定起点、长度的子串
• concat：将多个string进行拼接

--20210305转成2021-03-05
select concat(substr('20210305',1,4),'-',substr('20210305',5,2),'-',substr('20210305',7,2))
from table;

--2021-03-05转成20210305
select concat(substr('2021-03-05',1,4),substr('2021-03-05',7,2),substr('20210305',9,2))
from table;

方法三：regexp_replace

• regexp_replace：在字符串中搜索指定pattern（正则表达式模式)，并将该模式的每个匹配项替换为指定字符串

1. 语法: regexp_replace(string A, string B, string C)
2. 操作类型: strings
3. 返回值: string
4. 说明: 将字符串A中的符合正则表达式B的部分替换为C。

• 延伸 REGEXP：判断字符串是否满足pattern要求的模式

1. 语法1: A REGEXP B
2. 语法2: REGEXP(A, B)
3. 操作类型: strings
4. 返回类型: boolean或null
5. 描述: 功能与RLIKE相同

-- regexp_replace
-- 将'h234ney'中所有数字替换成字母o
select regexp_replace('h234ney', '\\d+', 'o') from table
output: honey

select regexp_replace('2021-03-05','-','') from table
output: 20210305

-- 从电子邮件中删除@和域名
select regexp_replace(email, '@.*(org|gov|com)$'
from table;

              email                | regexp_replace 
-----------------------------------+----------------  
  DonecFri@semperpretiumneque.com  | DonecFri
  mk1wait@UniOfTech.org            | mk1wait
  sed@redshiftemails.com           | sed
  bunyung@integermath.gov          | bunyung
  tomsupporter@galaticmess.org     | tomsupporter 


-- regexp
select 'football' regexp ('ba') from table
output: true

3、 对某个时间加上某些时间：date_add()

• 函数解析：date_add() 有两个参数，一个是基准日期, 另一个是希望增加的天数；例如2021年1月2日加上2天，等于20210104

select date_add(20210101,2) from table;
output: 20210103

4、 对某个时间减去某些时间：date_sub()

• 函数解析：date_sub() 有两个参数，一个是基准日期, 另一个是希望减去的天数；例如2021年1月2日减去2天，等于20201231

5、 求两个日期之间的差值：datediff()

• 函数解析：datediff()有两个参数，分别是日期A和日期B，输出是A和B的时间差