mysql时间函数总结

谁践踏了优雅 2022-03-20 12:14 250阅读 0赞

/*
    mysql 官网 时间函数 参考  https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
    */
    /*
    一、获取当前时间
    1.1 获得当前日期+时间（date + time）函数：now()
    这些日期时间函数，都等同于 now()。鉴于 now() 函数简短易记，建议总是使用 now() 来替代上面列出的函数。
    */
    SELECT NOW()  -- 2019-02-12 17:30:18
    SELECT CURRENT_TIMESTAMP() -- 2019-02-12 17:30:55
    SELECT LOCALTIME()  -- 2019-02-12 17:31:20
    SELECT LOCALTIMESTAMP() -- 2019-02-12 17:31:43
    
    /*
    1.2 获得当前日期+时间（date + time）函数：sysdate()
    sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。
    */
    SELECT SYSDATE() -- 2019-02-12 17:33:51
    
    /*
    2. 获得当前日期（date）函数：curdate()
    其中，下面的两个日期函数等同于 curdate()： current_date(),current_date
    */
    SELECT CURDATE() -- 2019-02-12
    SELECT CURRENT_DATE() -- 2019-02-12
    SELECT CURRENT_DATE -- 2019-02-12
    
    /*
    3. 获得当前时间（time）函数：curtime()
    其中，下面的两个时间函数等同于 curtime()：current_time(),current_time
    */
    SELECT CURTIME() -- 17:36:47
    
    /*
    4. 获得当前 UTC 日期时间函数：utc_date(), utc_time(), utc_timestamp()
    因为我国位于东八时区，所以本地时间 = UTC 时间 + 8 小时。UTC 时间在业务涉及多个国家和地区的时候，非常有用。
    */
    SELECT UTC_DATE() -- 2019-02-12
    SELECT UTC_TIME() -- 09:38:19
    SELECT UTC_TIMESTAMP() -- 2019-02-12 09:38:40

/*
    二、MySQL 日期时间函数。
    1. 选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒
    */
    
    SET @dt = '2008-09-10 07:15:30.123456'; -- 声明变量
      
    SELECT DATE(@dt); -- 2008-09-10
    SELECT TIME(@dt); -- 07:15:30.123456
    SELECT YEAR(@dt); -- 2008
    SELECT QUARTER(@dt); -- 3
    SELECT MONTH(@dt); -- 9
    SELECT WEEK(@dt); -- 36
    SELECT DAY(@dt); -- 10
    SELECT HOUR(@dt); -- 7
    SELECT MINUTE(@dt); -- 15
    SELECT SECOND(@dt); -- 30
    SELECT MICROSECOND(@dt); -- 123456
    
    SELECT YEAR( NOW() ) -- 2019
    SELECT MONTH( NOW() ) -- 2
    
    /*
    2. MySQL Extract() 函数，可以上面实现类似的功能：
    MySQL Extract() 函数除了没有date(),time() 的功能外，其他功能一应具全。并且还具有选取‘day_microsecond’ 等功能。
    注意这里不是只选取 day 和 microsecond，而是从日期的 day 部分一直选取到 microsecond 部分。
    MySQL Extract() 函数唯一不好的地方在于：你需要多敲几次键盘。
    
    */
    SET @dt = '2008-09-10 07:15:30.123456'; 
      
    SELECT EXTRACT(YEAR FROM @dt); -- 2008
    SELECT EXTRACT(QUARTER FROM @dt); -- 3
    SELECT EXTRACT(MONTH FROM @dt); -- 9
    SELECT EXTRACT(WEEK FROM @dt); -- 36
    SELECT EXTRACT(DAY FROM @dt); -- 10
    SELECT EXTRACT(HOUR FROM @dt); -- 7
    SELECT EXTRACT(MINUTE FROM @dt); -- 15
    SELECT EXTRACT(SECOND FROM @dt); -- 30
    SELECT EXTRACT(MICROSECOND FROM @dt); -- 123456
    SELECT EXTRACT(YEAR_MONTH FROM @dt); -- 200809
    SELECT EXTRACT(DAY_HOUR FROM @dt); -- 1007
    SELECT EXTRACT(DAY_MINUTE FROM @dt); -- 100715
    SELECT EXTRACT(DAY_SECOND FROM @dt); -- 10071530
    SELECT EXTRACT(DAY_MICROSECOND FROM @dt); -- 10071530123456
    SELECT EXTRACT(HOUR_MINUTE FROM @dt); -- 715
    SELECT EXTRACT(HOUR_SECOND FROM @dt); -- 71530
    SELECT EXTRACT(HOUR_MICROSECOND FROM @dt); -- 71530123456
    SELECT EXTRACT(MINUTE_SECOND FROM @dt); -- 1530
    SELECT EXTRACT(MINUTE_MICROSECOND FROM @dt); -- 1530123456
    SELECT EXTRACT(SECOND_MICROSECOND FROM @dt); -- 30123456
    
    SELECT EXTRACT(YEAR FROM NOW()); -- 2019
    
    /*
    3. MySQL dayof… 函数：dayofweek(), dayofmonth(), dayofyear()
    分别返回日期参数，在一周、一月、一年中的位置。
    日期 ‘2008-08-08′ 是一周中的第 6 天（1 = Sunday, 2 = Monday, …, 7 = Saturday）；一月中的第 8 天；一年中的第 221 天。
    
    */
    SET @dt = '2008-08-08';
    SELECT DAYOFWEEK(@dt); -- 6
    SELECT DAYOFMONTH(@dt); -- 8
    SELECT DAYOFYEAR(@dt); -- 221
    
    SELECT DAYOFWEEK(NOW()); -- 3 \ 实际是星期二
    SELECT DAYOFYEAR(NOW()); -- 43
    
    /*
    4. MySQL week… 函数：week(), weekofyear(), dayofweek(), weekday(), yearweek()
    
    MySQL week() 函数，可以有两个参数，具体可看官方手册。 weekofyear() 和 week() 一样，都是计算“某天”是位于一年中的第几周。 weekofyear(@dt) 等价于 week(@dt,3)。
    
    MySQL weekday() 函数和 dayofweek() 类似，都是返回“某天”在一周中的位置。不同点在于参考的标准， 
    weekday：(0 = Monday, 1 = Tuesday, …, 6 = Sunday)； 
    dayofweek：（1 = Sunday, 2 = Monday, …, 7 = Saturday）
    
    MySQL yearweek() 函数，返回 year(2008) + week 位置(31)。
    
    */
    
    SET @dt = '2008-08-08';
    SELECT WEEK(@dt); -- 31  默认情况下是把每周日作为一周的开始
    SELECT WEEK(@dt,0); -- 31  第二个参数是指每周的第一天是星期几 0为星期天
    /*
    第一周是怎么计算的？
    模式	星期的第一天	范围	
    0	Sunday	        0-53   
    遇到本年的第一个星期天开始是第一周，前面的计算为第0周
    1	Monday	        0-53	
    假如第一周能超过3天，那么计算为本年的第一周。否则为第0周
    可以理解为：
    如果1月1号～4号是星期一，那么本年第一周开始。（之前的是第零周）
    如果1月5号以后是星期一，那么现在开始，是本年的第二周了，之前的是第一周。
    也可以这样理解：
    如果包含1月1日的一周在新的一年中有4天或更多天，则为第1周。
    否则，它是上一年的最后一周，下一周是第1周。
    */
    SELECT WEEK(@dt,1); -- 32  
    SELECT WEEK(@dt,3); -- 32 
    SELECT WEEKOFYEAR(@dt); -- 32
    
    SELECT DAYOFWEEK(@dt); -- 6
    SELECT WEEKDAY(@dt); -- 4
    
    SELECT YEARWEEK(@dt); -- 200831
    
    SELECT DAYOFWEEK(NOW()); -- 3 /星期二
    SELECT WEEKDAY(NOW());   -- 1 /星期二 
    
    /*
    5. MySQL 返回星期和月份名称函数：dayname(), monthname()
    */
    SET @dt = NOW(); -- 2019-2-12  星期二 
    SELECT DAYNAME(@dt); -- Tuesday
    SELECT MONTHNAME(@dt); -- February
    
    /*
    6. MySQL last_day() 函数：返回月份中的最后一天。
    */
    SELECT LAST_DAY('2008-02-01'); -- 2008-02-29
    SELECT LAST_DAY('2008-08-08'); -- 2008-08-31

/**
    三、MySQL 日期时间计算函数
    */
    
    /*1. MySQL 为日期增加一个时间间隔：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 adddate(), addtime()函数，可以用 date_add() 来替代。下面是 date_add() 实现 addtime() 功能示例：*/
    
    SET @dt = '2008-08-09 12:12:33';
    SELECT DATE_ADD(@dt, INTERVAL '01:15:30' HOUR_SECOND); -- 2008-08-09 13:28:03 |
    SELECT DATE_ADD(@dt, INTERVAL '1 01:15:30' DAY_SECOND);-- 2008-08-10 13:28:03
    /**
    date_add() 函数，分别为 @dt 增加了“1小时 15分 30秒” 和 “1天 1小时 15分 30秒”。
    建议：总是使用 date_add() 日期时间函数来替代 adddate(), addtime()。
    **/
      
    /**
    2. MySQL 为日期减去一个时间间隔：date_sub()
    MySQL date_sub() 日期时间函数 和 date_add() 用法一致，不再赘述。另外，MySQL 中还有两个函数 subdate(), subtime()，建议，用 date_sub() 来替代。
    */
    
    /*
    3. MySQL 另类日期函数：period_add(P,N), period_diff(P1,P2)
    函数参数“P” 的格式为“YYYYMM” 或者 “YYMM”，第二个参数“N” 表示增加或减去 N month（月）。
    MySQL period_add(P,N)：日期加/减去N月。**/
    SET @dt = '201902';
    SELECT	PERIOD_ADD(@dt,-1) -- 201901
    
    /*
    4. MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)
    MySQL datediff(date1,date2)：两个日期相减 date1 - date2，返回天数。*/
    SELECT DATEDIFF('2008-08-08', '2008-08-01'); -- 7
    SELECT DATEDIFF('2008-08-01', '2008-08-08'); -- -7
    /*MySQL timediff(time1,time2)：两个日期相减 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
    /*注意：timediff(time1,time2) 函数的两个参数类型必须相同。*/

/*四、MySQL 日期转换函数、时间转换函数*/
    /*1. MySQL （时间、秒）转换函数：time_to_sec(time), sec_to_time(seconds)*/
    SELECT TIME_TO_SEC('01:00:05'); -- 3605
    SELECT SEC_TO_TIME(3605); -- '01:00:05'
      
    /*2. MySQL （日期、天数）转换函数：to_days(date), from_days(days)*/
    SELECT TO_DAYS('0000-00-00'); -- 0
    SELECT TO_DAYS('2008-08-08'); -- 733627
    SELECT FROM_DAYS(0); -- '0000-00-00'
    SELECT FROM_DAYS(733627); -- '2008-08-08'
    SELECT FROM_DAYS(370); -- '0001-01-05'
    
    /*3. MySQL Str to Date （字符串转换为日期）函数：str_to_date(str, format)*/
    SELECT STR_TO_DATE('08/09/2008', '%m/%d/%Y'); -- 2008-08-09
    SELECT STR_TO_DATE('08/09/08' , '%m/%d/%y'); -- 2008-08-09
    SELECT STR_TO_DATE('08.09.2008', '%m.%d.%Y'); -- 2008-08-09
    SELECT STR_TO_DATE('08:09:30', '%h:%i:%s'); -- 08:09:30
    SELECT STR_TO_DATE('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30
    /*可以看到，str_to_date(str,format) 转换函数，可以把一些杂乱无章的字符串转换为日期格式。另外，它也可以转换为时间。“format” 可以参看 MySQL 手册。*/
      
    /*4. MySQL Date/Time to Str（日期/时间转换为字符串）函数：date_format(date,format), time_format(time,format)
    MySQL 日期、时间转换函数：date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。
     */
     SELECT DATE_FORMAT(NOW(),'%y-%m-%d %h:%i:%s') -- 19-02-12 08:38:39
     SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i:%s') -- 2019-02-12 08:39:06
      
    /*5. MySQL 拼凑日期、时间函数：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'

/*五、MySQL 时间戳（Timestamp）函数*/
    /*1. MySQL 获得当前时间戳函数：current_timestamp, current_timestamp()*/
    SELECT CURRENT_TIMESTAMP() -- 2019-02-12 20:41:02
    SELECT CURRENT_TIMESTAMP  --  2019-02-12 20:41:02
    
    /*2. MySQL （Unix 时间戳、日期）转换函数：*/
    SELECT UNIX_TIMESTAMP() -- 1549975329
    SELECT UNIX_TIMESTAMP(NOW()) -- 1549975356
    SELECT FROM_UNIXTIME(1549975329) -- 2019-02-12 20:42:09
    SELECT FROM_UNIXTIME(1549975329,'%Y年%m月%d日 %h:%i:%s') -- 2019年02月12日 08:42:09