MySQL 日期和时间函数实践（宝藏）

今天我们一起来了解下在MySQL中比较常用的一些时间和日期函数，希望可以给您带来一些帮助。

🕑 获取当前时间

获取当前时间使用最多的函数一般是now。

select now();

-- 2024-06-20 06:47:59

默认精度到秒，可以通过参数的形式精确到毫秒。

select now(6);

-- 2024-06-20 07:35:42.887635

当然也会使用下面的函数获取当前时间（其实都是now的同义词）。

select current_timestamp();

select localtime();

select localtimestamp();

🤖 sysdate和now的区别

其实在MySQL中还有一个sysdate函数。那么sysdate和now有什么区别呢？

sysdate是nondeterministic 的，而now 是deterministic的。

with recursive aa
	as (select 1 n,now(6) d
	union all
	select n +1 n ,now(6) d
	from aa where n < 5)
select * from aa ;

-- 1 2024-06-20 07:20:16.442852
-- 2 2024-06-20 07:20:16.442852
-- 3 2024-06-20 07:20:16.442852
-- 4 2024-06-20 07:20:16.442852
-- 5 2024-06-20 07:20:16.442852

从上面的脚本种可以看出，结果中的时间字段都是一样的，即now返回语句开始执行的常量时间。

with recursive aa
	as (select 1 n,sysdate(6) d
	union all
	select n +1 n ,sysdate(6) d
	from aa where n < 5)
select * from aa ;

-- 1 2024-06-20 07:20:53.052790
-- 2 2024-06-20 07:20:53.052804
-- 3 2024-06-20 07:20:53.052806
-- 4 2024-06-20 07:20:53.052808
-- 5 2024-06-20 07:20:53.052810

而sysdate返回执行的时间。

🦔 时间戳函数

✅ 获取当前时间戳

select unix_timestamp()

-- 1718868515

✅ 时间戳到日期

select from_unixtime(1718868515)

-- 2024-06-20 07:28:35

所以from_unixtime(0) 返回结果当然就是1970-01-01 00:00:00。

👻 日期格式化函数

date_format在开发中使用比较多，主要用于格式化日期和时间格式。

select date_format('2024-06-20 06:47:59', '%Y-%m-%d %H:%i:%s');

-- 2024-06-20 06:47:59

select date_format('2024-06-20 06:47:59', get_format(datetime, 'ISO'));

-- 2024-06-20 06:47:59

select date_format('2024-06-20 06:47:59', '%Y-%m-%d');

-- 2024-06-20

当然，上面这个脚本等同于：

select date('2024-06-20 06:47:59')

-- 2024-06-20

🐌 用extract提取日期

extract函数可以从日期中提取几乎所有时间单位。

✳️年月周

select extract(year from '2024-06-20 06:47:59');
-- 2024

select extract(year_month from '2024-06-20 06:47:59');
-- 202406

select extract(month from '2024-06-20 06:47:59');
-- 6

select extract(week from '2024-06-20 06:47:59');
-- 24

✳️以day开始

select extract(day from '2024-06-20 06:47:59');
-- 20

select extract(day_hour from '2024-06-20 06:47:59');
-- 2006

select extract(day_minute from '2024-06-20 06:47:59');
-- 200647 

select extract(day_second from '2024-06-20 06:47:59');
-- 20064759

select extract(day_microsecond from '2024-06-20 06:47:59');
-- 20064759000000

✳️以hour开始

select extract(hour from '2024-06-20 06:47:59');
-- 6

select extract(hour_minute from '2024-06-20 06:47:59');
-- 647

select extract(hour_second from '2024-06-20 06:47:59');
-- 64759
  
select extract(hour_microsecond from '2024-06-20 06:47:59');
-- 64759000000

✳️以minute开始

select extract(minute from '2024-06-20 06:47:59');
-- 47  

select extract(minute_second from '2024-06-20 06:47:59');
-- 4759

select extract(minute_microsecond from '2024-06-20 06:47:59');
-- 4759000000

✳️以second开始

select extract(second from '2024-06-20 06:47:59');
-- 59

select extract(second_microsecond from '2024-06-20 06:47:59');
-- 59000000

🐨 使用日期函数提取日期

select year(now())
-- 2024

select quarter(now());
-- 2

select month(now());
-- 6

select week(now());
-- 24

select day(now());
-- 20 

select hour(now());
-- 7

select minute(now());
-- 26

select second(now())
-- 35
  
select microsecond(now(6));
-- 741617

🍒 时区转化

select convert_tz('2024-06-19 23:16:27','-7:00','GMT') as result
---
| 2024-06-20 06:16:27 |

  
select convert_tz('2024-06-19 23:16:27','-7:00','+8:00') as result
--- 
| 2024-06-20 14:16:27 |

➕ 日期运算

✴️ 直接加减

select '2024-06-20 08:11:16' + interval 1 day;
-- 2024-06-21 08:11:16


select '2024-06-20 08:11:16' + interval 1 week;
-- 2024-06-27 08:11:16


select '2024-06-20 08:11:16' + interval 2 year;
-- 2026-06-20 08:11:16

  
select '2024-06-20 08:11:16' + interval 2 minute;
-- 2024-06-20 08:13:16

select '2024-06-20 08:11:16' - interval 2 minute;
-- 2024-06-20 08:09:16

✴️ 使用函数

select date_add('2024-06-20 08:11:16', interval 1 day)
-- 2024-06-21 08:11:16

select date_sub('2024-06-20 08:11:16', interval 1 day)
-- 2024-06-19 08:11:16