MySQL日期时间函数(转换、计算)整理总结

2021-10-06 23点热度 0人点赞 0条评论

本篇介绍MySQL下和时间、日期相关的函数

以下SQL命令,大小写都是可以的

MySQL中的时间单位

MySQL时间单位说明

year
quarter 季度
month
week
day
hour 小时
hour 分钟
second
microsecond 微秒

获得当前时间

-- 获得当前的时间+日期
SELECT NOW(); -- 得到 2021-10-06 09:02:28

-- 获得当前的时间+日期,也可以使用sysdate()函数
SELECT sysdate(); -- 得到 2021-10-06 09:02:28

-- sysdate() 日期时间函数跟 now() 类似,不同之处在于:now() 在执行开始时值就得到了, sysdate() 在函数执行时动态得到值。例子如下,我们在now()后延迟了3秒钟
select now(), sleep(3), now(), sysdate();

mysql> select now(), sleep(3), now(),sysdate();
+---------------------+----------+---------------------+---------------------+
| now() | sleep(3) | now() | sysdate() |
+---------------------+----------+---------------------+---------------------+
| 2021-10-06 09:07:40 | 0 | 2021-10-06 09:07:40 | 2021-10-06 09:07:43 |
+---------------------+----------+---------------------+---------------------+
1 row in set (3.00 sec)

-- 可以看到第二个now获得的时间和第一个now是一样的,但是sysdate获得的时间比now多了3秒

-- 得当前时间戳
SELECT CURRENT_TIMESTAMP()

日期转换函数

Date/Time to Str(日期/时间转换为字符串)函数:date_format(date,format), time_format(time,format)

mysql> select date_format('2021-10-06 09:23:01', '%Y%m%d%H%i%s');
+----------------------------------------------------+
| date_format('2021-10-06 09:23:01', '%Y%m%d%H%i%s') |
+----------------------------------------------------+
| 20211006092301                                     |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(now(), '%Y-%m-%d %H:%i:%s');
+-----------------------------------------+
| date_format(now(), '%Y-%m-%d %H:%i:%s') |
+-----------------------------------------+
| 2021-10-06 09:12:37                     |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select time_format(now(), '%H:%i:%s');
+--------------------------------+
| time_format(now(), '%H:%i:%s') |
+--------------------------------+
| 09:13:00                       |
+--------------------------------+
1 row in set (0.00 sec)

Str to Date (字符串转换为日期)函数:str_to_date(str, format)

mysql> select str_to_date('2021-10-01', '%Y-%m-%d');
+---------------------------------------+
| str_to_date('2021-10-01', '%Y-%m-%d') |
+---------------------------------------+
| 2021-10-01                            |
+---------------------------------------+
1 row in set (0.01 sec)

mysql> select str_to_date('2021-10-01 00:00:00', '%Y-%m-%d %H:%i:%s');
+---------------------------------------------------------+
| str_to_date('2021-10-01 00:00:00', '%Y-%m-%d %H:%i:%s') |
+---------------------------------------------------------+
| 2021-10-01 00:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

(日期、天数)转换函数:to_days(date), from_days(days)

计算从0000年1月1日 以来的天数(注意不是1970年)

mysql> select to_days('0000-1-1');
+---------------------+
| to_days('0000-1-1') |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

mysql> select to_days('2021-10-06');
+-----------------------+
| to_days('2021-10-06') |
+-----------------------+
|                738434 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select from_days(738434); -- 2021-10-06

(时间、秒)转换函数:time_to_sec(time), sec_to_time(seconds)

计算一天内时间的秒数

select time_to_sec('01:00:05'); -- 3605
select sec_to_time(3605); -- '01:00:05'

拼凑日期、时间函数:makdedate(year,dayofyear), maketime(hour,minute,second)

select makedate(2001,31); -- '2001-01-31'
select makedate(2001,32); -- '2001-02-01'
select maketime(12,15,30); -- '12:15:30'

日期、时间计算函数介绍

在日期上增加一个时间间隔

select date_add(now(), interval 1 day); -- 增加1天
select date_add(now(), interval 1 hour); -- 增加1小时

-- interval 后面,可以跟着的其他时间单位,(详见最上面时间单位介绍):
-- day 天
-- hour 小时
-- minute 分钟
-- second 秒
-- microsecond 微秒
-- week 周
-- month 月
-- quarter 季度
-- year 年

-- interval后面还可以跟着负数,表示减少,例如:
select date_add(now(), interval -1 day); -- 减少1天

日期、时间相减/相差函数:datediff(date1,date2), timediff(time1,time2)

-- 返回相差的天数
select datediff('2021-10-01', '2021-10-06'); -- 相差-5天
select datediff('2021-10-06', '2021-10-01'); -- 相差5天

-- 两个日期相减 time1 - time2,返回 time 差值
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)转换、增、减、相差函数

timestamp(date) -- date to timestamp 日期转成timestamp
timestamp(dt, time) -- dt + time
timestampadd(unit, interval, datetime_expr) --
timestampdiff(unit, datetime_expr1, datetime_expr2) --比较两个时间戳相差多少个单位,例如相差多小小时,多少天
-- unit 可以是year 年,month 月,day天等时间单位

示例:

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

时区转换函数

convert_tz(dt,from_tz,to_tz)

select convert_tz('2021-08-08 12:00:00', '+08:00', '+00:00'); -- 2021-08-08 04:00:00

admin

这个人很懒,什么都没留下

文章评论

*

code