首页 大杂烩正文

mysql时间日期函数介绍

原创
小哥 3年前 (2022-10-11) 阅读数 196 #大杂烩

一、MySQL常见的日期函数-------------

1.1、有\时间

# 获得当前日期和时间    select NOW(); # 2021-04-02 09:25:29
# 获得当前日期    SELECT CURDATE(); # 2021-04-02
# 获取当前时间    SELECT CURTIME();  # 09:26:10

# 对于时间2021-04-02 09:25:29，分别获得的年和月、日、时、分、秒
SELECT EXTRACT(YEAR FROM NOW()); # 2021
SELECT EXTRACT(MONTH FROM NOW()); # 4
SELECT EXTRACT(DAY FROM NOW()); # 2
SELECT EXTRACT(HOUR FROM NOW()); # 9
SELECT EXTRACT(MINUTE FROM NOW()); # 25
SELECT EXTRACT(SECOND FROM NOW()); # 29

# 或日期格式的字符串    SELECT EXTRACT(SECOND FROM 2021-04-02 10:37:14.123456);  # 14

1.2、日期增加和减少少

# 时间减少1小时（前一小时）
select date_sub(now(), INTERVAL 1 hour);

# 日期增加1天
select date_add(now(), INTERVAL 1 day);

# 其他时间间隔    INTERVAL 1 YEAR
INTERVAL 1 MONTH
INTERVAL 1 DAY
INTERVAL 1 HOUR
INTERVAL 1 MINUTE
INTERVAL 1 SECOND

1.3、日期格式,文字字符串的日期

# 格式参考:
select DATE_FORMAT(now(),%Y-%m-%d %H:%i:%s);
select DATE_FORMAT(now(),%Y-%m-%d %H:00:00);

#字字符串的日期    select str_to_date(2021-04-02 10:37:14, %Y-%m-%d %H:%i:%s); # 2021-04-02 10:37:14

二、其他引用函数--------

以下是更完整的MySQL(原日期函数可以作为参考文链接：https://blog.csdn.net/qinshijangshan/article/details/72874667）

-- MySQL日期时间处理函数    -- 当前日期:2017-05-12（突然发现今天512，将拉防空警报)
SELECT NOW() FROM DUAL;-- 当前的日期和时间:2017-05-12 11:41:47
-- 在MySQL也有和Oracle里类似的dual虚拟表:官方宣称它是纯粹的满足select ... from...这习惯问题,mysql对表的引用将被忽略。
-- 那么MySQL中就不用DUAL了吧。
SELECT NOW();-- 当前的日期和时间:2017-05-12 11:41:55
-- 除了 now() 函数可以获得当前日期和时间,MySQL 也有以下功能:
SELECT CURRENT_TIMESTAMP();-- 2017-05-15 10:19:31
SELECT CURRENT_TIMESTAMP;-- 2017-05-15 10:19:51
SELECT LOCALTIME();-- 2017-05-15 10:20:00
SELECT LOCALTIME;-- 2017-05-15 10:20:10
SELECT LOCALTIMESTAMP();-- 2017-05-15 10:20:21(v4.0.6)
SELECT LOCALTIMESTAMP;-- 2017-05-15 10:20:30(v4.0.6)
-- 这些日期时间函数等同于 now()。鉴于 now() 函数是短暂的和容易记住总是使用now()替换上面列出的功能。

SELECT SYSDATE();-- 当前的日期和时间:2017-05-12 11:42:03
-- sysdate() 日期时间函数和now() 类似，
-- 不同之处在于:now() 获得的值开始执行;sysdate() 获取值时动态地执行的函数。
-- 很明显从下面的例子:
SELECT NOW(), SLEEP(3), NOW();
SELECT SYSDATE(), SLEEP(3), SYSDATE();

SELECT CURDATE();-- 当前日期:2017-05-12
SELECT CURRENT_DATE();-- 当前日期:等同于 CURDATE()
SELECT CURRENT_DATE;-- 当前日期:等同于 CURDATE()

SELECT CURTIME();-- 当前时间:11:42:47
SELECT CURRENT_TIME();-- 当前时间:等同于 CURTIME()
SELECT CURRENT_TIME;-- 当前时间:等同于 CURTIME()

-- 得到当前UTC 日期时间函数    SELECT UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME()
-- MySQL 获取当前时间戳函数:current_timestamp, current_timestamp()
SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP();-- 2017-05-15 10:32:21 | 2017-05-15 10:32:21

-- MySQL 日期时间Extract（选取） 函数
SET @dt = 2017-05-15 10:37:14.123456;
SELECT DATE(@dt);-- 收购:2017-05-15
SELECT TIME(2017-05-15 10:37:14.123456);-- 采集时间:10:37:14.123456
SELECT YEAR(2017-05-15 10:37:14.123456);-- 获得一年    SELECT MONTH(2017-05-15 10:37:14.123456);-- 让月    SELECT DAY(2017-05-15 10:37:14.123456);-- 获取日
SELECT HOUR(2017-05-15 10:37:14.123456);-- 获取时
SELECT MINUTE(2017-05-15 10:37:14.123456);-- 获取分
SELECT SECOND(2017-05-15 10:37:14.123456);-- 获取秒
SELECT MICROSECOND(2017-05-15 10:37:14.123456);-- 得到毫秒    SELECT QUARTER(2017-05-15 10:37:14.123456);-- 获得季    SELECT WEEK(2017-05-15 10:37:14.123456);-- 20 (获取周)
SELECT WEEK(2017-05-15 10:37:14.123456, 7);-- ****** 测试这个函数MySQL5.6下无效
SELECT WEEKOFYEAR(2017-05-15 10:37:14.123456);-- 同week()
SELECT DAYOFYEAR(2017-05-15 10:37:14.123456);-- 135 (的一天)
SELECT DAYOFMONTH(2017-05-15 10:37:14.123456);-- 5 (月的一天)
SELECT DAYOFWEEK(2017-05-15 10:37:14.123456);-- 2 (这个星期的一天;天是第一天)
SELECT WEEKDAY(2017-05-15 10:37:14.123456);-- 0
SELECT WEEKDAY(2017-05-21 10:37:14.123456);-- 6(与dayofweek()都表示一周的日子参考标准是不同的,weekday()周一为第0天，周日为第6天)
SELECT YEARWEEK(2017-05-15 10:37:14.123456);-- 201720(年和周)

SELECT EXTRACT(YEAR FROM 2017-05-15 10:37:14.123456);
SELECT EXTRACT(MONTH FROM 2017-05-15 10:37:14.123456);
SELECT EXTRACT(DAY FROM 2017-05-15 10:37:14.123456);
SELECT EXTRACT(HOUR FROM 2017-05-15 10:37:14.123456);
SELECT EXTRACT(MINUTE FROM 2017-05-15 10:37:14.123456);
SELECT EXTRACT(SECOND FROM 2017-05-15 10:37:14.123456);
SELECT EXTRACT(MICROSECOND FROM 2017-05-15 10:37:14.123456);
SELECT EXTRACT(QUARTER FROM 2017-05-15 10:37:14.123456);
SELECT EXTRACT(WEEK FROM 2017-05-15 10:37:14.123456);
SELECT EXTRACT(YEAR_MONTH FROM 2017-05-15 10:37:14.123456);
SELECT EXTRACT(DAY_HOUR FROM 2017-05-15 10:37:14.123456);
SELECT EXTRACT(DAY_MINUTE FROM 2017-05-15 10:37:14.123456);-- 151037(日时分)
SELECT EXTRACT(DAY_SECOND FROM 2017-05-15 10:37:14.123456);-- 15103714(天小时分钟秒)
SELECT EXTRACT(DAY_MICROSECOND FROM 2017-05-15 10:37:14.123456);-- 15103714123456(天小时分钟第二毫秒)
SELECT EXTRACT(HOUR_MINUTE FROM 2017-05-15 10:37:14.123456);-- 1037(时分)
SELECT EXTRACT(HOUR_SECOND FROM 2017-05-15 10:37:14.123456);-- 103714(时分秒)
SELECT EXTRACT(HOUR_MICROSECOND FROM 2017-05-15 10:37:14.123456);-- 103714123456(天小时分钟第二毫秒)
SELECT EXTRACT(MINUTE_SECOND FROM 2017-05-15 10:37:14.123456);-- 3714(分秒)
SELECT EXTRACT(MINUTE_MICROSECOND FROM 2017-05-15 10:37:14.123456);-- 3714123456(分钟秒毫秒)
SELECT EXTRACT(SECOND_MICROSECOND FROM 2017-05-15 10:37:14.123456);-- 14123456(秒毫秒)
-- MySQL Extract() 除了这些功能date(),time() 除了它的功能他应该有他所有的功能。
-- 它也有一个选择的day_microsecond 等功能。
-- 请注意,这不仅仅是一个选择day 和 microsecond，但从日期day 选择一部分,直到microsecond 部分。

SELECT DAYNAME(2017-05-15 10:37:14.123456);-- Monday(回到英语周)
SELECT MONTHNAME(2017-05-15 10:37:14.123456);-- May(回到英语月)
SELECT LAST_DAY(2016-02-01);-- 2016-02-29 (返回到本月最后一天)
SELECT LAST_DAY(2016-05-01);-- 2016-05-31

-- DATE_ADD(date,INTERVAL expr type) 从日期+指定时间间隔    -- type参数可以参考:http://www.w3school.com.cn/sql/func_date_sub.asp
SELECT DATE_ADD(2017-05-15 10:37:14.123456,INTERVAL 1 YEAR);-- 表示：2018-05-15 10:37:14.123456
SELECT DATE_ADD(2017-05-15 10:37:14.123456,INTERVAL 1 QUARTER);-- 表示：2017-08-15 10:37:14.123456
SELECT DATE_ADD(2017-05-15 10:37:14.123456,INTERVAL 1 MONTH);-- 表示：2017-06-15 10:37:14.123456
SELECT DATE_ADD(2017-05-15 10:37:14.123456,INTERVAL 1 WEEK);-- 表示：2017-05-22 10:37:14.123456
SELECT DATE_ADD(2017-05-15 10:37:14.123456,INTERVAL 1 DAY);-- 表示：2017-05-16 10:37:14.123456
SELECT DATE_ADD(2017-05-15 10:37:14.123456,INTERVAL 1 HOUR);-- 表示：2017-05-15 11:37:14.123456
SELECT DATE_ADD(2017-05-15 10:37:14.123456,INTERVAL 1 MINUTE);-- 表示：2017-05-15 10:38:14.123456
SELECT DATE_ADD(2017-05-15 10:37:14.123456,INTERVAL 1 SECOND);-- 表示：2017-05-15 10:37:15.123456
SELECT DATE_ADD(2017-05-15 10:37:14.123456,INTERVAL 1 MICROSECOND);-- 表示：2017-05-15 10:37:14.123457

-- DATE_SUB(date,INTERVAL expr type) 从日期减去指定的时间间隔    SELECT DATE_SUB(2017-05-15 10:37:14.123456,INTERVAL 1 YEAR);-- 表示：2016-05-15 10:37:14.123456
SELECT DATE_SUB(2017-05-15 10:37:14.123456,INTERVAL 1 QUARTER);-- 表示：2017-02-15 10:37:14.123456
SELECT DATE_SUB(2017-05-15 10:37:14.123456,INTERVAL 1 MONTH);-- 表示：2017-04-15 10:37:14.123456
SELECT DATE_SUB(2017-05-15 10:37:14.123456,INTERVAL 1 WEEK);-- 表示：2017-05-08 10:37:14.123456
SELECT DATE_SUB(2017-05-15 10:37:14.123456,INTERVAL 1 DAY);-- 表示：2017-05-14 10:37:14.123456
SELECT DATE_SUB(2017-05-15 10:37:14.123456,INTERVAL 1 HOUR);-- 表示：2017-05-15 09:37:14.123456
SELECT DATE_SUB(2017-05-15 10:37:14.123456,INTERVAL 1 MINUTE);-- 表示：2017-05-15 10:36:14.123456
SELECT DATE_SUB(2017-05-15 10:37:14.123456,INTERVAL 1 SECOND);-- 表示：2017-05-15 10:37:13.123456
SELECT DATE_SUB(2017-05-15 10:37:14.123456,INTERVAL 1 MICROSECOND);-- 表示：2017-05-15 10:37:14.123455

-- 测试在一个特殊的日期,DATE_SUB(date,INTERVAL expr type)可以用信心    SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY);-- 前一天：2017-05-11
SELECT DATE_SUB(CURDATE(),INTERVAL -1 DAY);-- 后一天：2017-05-13
SELECT DATE_SUB(CURDATE(),INTERVAL 1 MONTH);-- 一个月前日期:2017-04-12
SELECT DATE_SUB(CURDATE(),INTERVAL -1 MONTH);-- 一个月后:2017-06-12
SELECT DATE_SUB(CURDATE(),INTERVAL 1 YEAR);-- 一年前的日期:2016-05-12
SELECT DATE_SUB(CURDATE(),INTERVAL -1 YEAR);-- 一年后:20178-06-12
-- MySQL date_sub() Datetime函数和 date_add() 一致的使用,且可以用INTERNAL -1 xxx的形式交替使用;
-- 另外，MySQL 也有两个函数subdate(), subtime()，建议，用 date_sub() 来替代。

-- MySQL 选择日期函数:period_add(P,N), period_diff(P1,P2)
-- 函数参数”P” 的格式是“YYYYMM” 或者 “YYMM”，第二个参数”N” 表示加法或减法N month（月）。
-- MySQL period_add(P,N)：日期+ / -去N月。
SELECT PERIOD_ADD(201705,2), PERIOD_ADD(201705,-2);-- 201707  20170503
-- period_diff(P1,P2)：日期 P1-P2，返回 N 个月。
SELECT PERIOD_DIFF(201706, 201703);-- 
-- datediff(date1,date2)：减去两个日期date1 - date2，回来的日子    SELECT DATEDIFF(2017-06-05,2017-05-29);-- 7
-- TIMEDIFF(time1,time2)：减去两个日期time1 - time2，返回 TIME 差值
SELECT TIMEDIFF(2017-06-05 19:28:37, 2017-06-05 17:00:00);-- 02:28:37

-- MySQL日期转换函数    SELECT TIME_TO_SEC(01:00:05); -- 3605
SELECT SEC_TO_TIME(3605);-- 01:00:05

-- MySQL （日期、天数）转换函数:to_days(date), from_days(days)
SELECT TO_DAYS(0000-00-00); -- NULL 
SELECT TO_DAYS(2017-06-05); -- 736850
SELECT FROM_DAYS(0);           -- 0000-00-00 
SELECT FROM_DAYS(736850);      -- 2017-06-05

-- MySQL Str to Date （字符串日期)的信数：str_to_date(str, format)

SELECT STR_TO_DATE(06.05.2017 19:40:30, %m.%d.%Y %H:%i:%s);-- 2017-06-05 19:40:30
SELECT STR_TO_DATE(06/05/2017, %m/%d/%Y);                  -- 2017-06-05
SELECT STR_TO_DATE(2017/12/3,%Y/%m/%d)             -- 2017-12-03
SELECT STR_TO_DATE(20:09:30, %h:%i:%s)             -- NULL(超过12小时是小写的时刻h，结果是NULL)

-- 日期时间格式    SELECT DATE_FORMAT(2017-05-12 17:03:51, %Y年%m月%d日 %H时%i分%s秒);-- 2017年05月12日 17时03分51秒(具身体所需的数据的格式取决于实际情况;写h为12小时制;)
SELECT TIME_FORMAT(2017-05-12 17:03:51, %Y年%m月%d日 %H时%i分%s秒);-- 0000年00月00日 17时03分51秒(time_format()只能用于时间格式)
-- STR_TO_DATE()和DATE_FORMATE()它是互操作     
-- MySQL 获得国家/地区时间格式功能:get_format()
-- MySQL get_format() 语法：get_format(date|time|datetime, eur|usa|jis|iso|internal
-- MySQL get_format() 完整的示例用法:
SELECT GET_FORMAT(DATE,usa);    -- %m.%d.%Y 
SELECT GET_FORMAT(DATE,jis);    -- %Y-%m-%d 
SELECT GET_FORMAT(DATE,iso);    -- %Y-%m-%d 
SELECT GET_FORMAT(DATE,eur);    -- %d.%m.%Y 
SELECT GET_FORMAT(DATE,internal);       -- %Y%m%d 
SELECT GET_FORMAT(DATETIME,usa);    -- %Y-%m-%d %H.%i.%s 
SELECT GET_FORMAT(DATETIME,jis);    -- %Y-%m-%d %H:%i:%s 
SELECT GET_FORMAT(DATETIME,iso);    -- %Y-%m-%d %H:%i:%s 
SELECT GET_FORMAT(DATETIME,eur);    -- %Y-%m-%d %H.%i.%s 
SELECT GET_FORMAT(DATETIME,internal); -- %Y%m%d%H%i%s 
SELECT GET_FORMAT(TIME,usa);    -- %h:%i:%s %p 
SELECT GET_FORMAT(TIME,jis);    -- %H:%i:%s 
SELECT GET_FORMAT(TIME,iso);    -- %H:%i:%s 
SELECT GET_FORMAT(TIME,eur);    -- %H.%i.%s 
SELECT GET_FORMAT(TIME,internal);     -- %H%i%s

-- MySQL 拼凑的日期和时间间函数：makdedate(year,dayofyear), maketime(hour,minute,second)
SELECT MAKEDATE(2017,31);   -- 2017-01-31 
SELECT MAKEDATE(2017,32);   -- 2017-02-01
SELECT MAKETIME(19,52,35);  -- 19:52:35

-- MySQL 时区（timezone）转换函数:convert_tz(dt,from_tz,to_tz)
SELECT CONVERT_TZ(2017-06-05 19:54:12, +08:00, +00:00); -- 2017-06-05 11:54:12

-- MySQL （Unix 时间戳,天期）转换函数
-- unix_timestamp(), unix_timestamp(date), from_unixtime(unix_timestamp), from_unixtime(unix_timestamp,format)
-- 特定的时间转换为timestamp
SELECT UNIX_TIMESTAMP();-- 当前时间的时间戳:1494815779
SELECT UNIX_TIMESTAMP(2017-05-15);-- 指定日期时间戳:1494777600
SELECT UNIX_TIMESTAMP(2017-05-15 10:37:14);-- 时间戳的指定日期时间:1494815834

-- 时间戳转换为特定的时间    SELECT FROM_UNIXTIME(1494815834);-- 2017-05-15 10:37:14
SELECT FROM_UNIXTIME(1494815834, %Y年%m月%d日 %h时%分:%s秒);-- 获取日期和时间的格式对应的时间戳     
-- MySQL 时间戳（timestamp）转换、增、减函数
SELECT TIMESTAMP(2017-05-15);-- 2017-05-15 00:00:00
SELECT TIMESTAMP(2017-05-15 08:12:25, 01:01:01);-- 2017-05-15 09:13:26
SELECT DATE_ADD(2017-05-15 08:12:25, INTERVAL 1 DAY);-- 2017-05-16 08:12:25
SELECT TIMESTAMPADD(DAY, 1, 2017-05-15 08:12:25);-- 2017-05-16 08:12:25; MySQL timestampadd() 函数类似date_add()。

SELECT TIMESTAMPDIFF(YEAR, 2017-06-01, 2016-05-15);-- -1
SELECT TIMESTAMPDIFF(MONTH, 2017-06-01, 2016-06-15);-- -11
SELECT TIMESTAMPDIFF(DAY, 2017-06-01, 2016-06-15);-- -351
SELECT TIMESTAMPDIFF(HOUR, 2017-06-01 08:12:25, 2016-06-15 00:00:00);-- -8432
SELECT TIMESTAMPDIFF(MINUTE, 2017-06-01 08:12:25, 2016-06-15 00:00:00);-- -505932
SELECT TIMESTAMPDIFF(SECOND, 2017-06-01 08:12:25, 2016-06-15 00:00:00);-- -30355945
版权声明

所有资源都来源于爬虫采集,如有侵权请联系我们,我们将立即删除

上一篇：C/C++内存分配详解 下一篇：各大平台免费的翻译API介绍
相关文章
小哥

小哥

作者文章
浏览更多
热门
最新文章
标签列表
粤ICP备19068341号
所有资源都来源于爬虫采集,如有侵权请联系我们,我们将立即删除
Powered By Z-BlogPHP Theme By 编程老白