MySQL日期时间函数

1、日期格式化：DATE_FORMAT(date,format)

     SELECT DATE_FORMAT(now(), '%Y-%m-%d');

     结果：2020-01-04

2、时间戳：UNIX_TIMESTAMP()，UNIX_TIMESTAMP(date)

     SELECT UNIX_TIMESTAMP()

  结果：1578153160

     SELECT UNIX_TIMESTAMP(NOW()); 

  结果：1578153160

3、时间戳格式化：FROM_UNIXTIME(unix_timestamp)，FROM_UNIXTIME(unix_timestamp, format)

  1）SELECT FROM_UNIXTIME(1578153160);
  结果：2020-01-04

  2）SELECT FROM_UNIXTIME(1578153160, '%Y-%m-%d');
  结果：2020-01-04

4、SELECT NOW()，CURDATE()，CURTIME()

5、CURRENT_TIMESTAMP()和NOW()相同获得的是语句开始执行时的时间;SYSDATE()获取的是执行时候的时间

     SELECT
     NOW(),CURRENT_TIMESTAMP(),SYSDATE(),
     SLEEP(5),
     NOW(),CURRENT_TIMESTAMP(),SYSDATE(),
     SLEEP(10),
     NOW(),CURRENT_TIMESTAMP(),SYSDATE();

6、字符转日期：STR_TO_DATE(str,format)

     SELECT STR_TO_DATE('01.06.2020 16:04:30', '%m.%d.%Y %H:%i:%s');

      结果：2020-01-06 16:04:30

     SELECT STR_TO_DATE('01/06/2020', '%m/%d/%Y');

     结果：2020-01-06
     SELECT STR_TO_DATE('01/06/20' , '%m/%d/%y');

    结果：2020-01-06
     SELECT STR_TO_DATE('01.06.2020', '%m.%d.%Y');

     结果：2020-01-06
     SELECT STR_TO_DATE('16:04:30', '%H:%i:%s');

     结果：16:04:30

7、TO_DAYS(date)，给定一个日期date, 返回一个天数 (从年份0开始的天数 )

      SELECT TO_DAYS('0000-01-06');

      结果：6

      SELECT TO_DAYS('2020-01-06');

      结果：737795

      SELECT TO_DAYS(NOW());

      结果：737795

8、时间转秒：TIME_TO_SEC(time)，秒转时间：SEC_TO_TIME(seconds)，注：这两个函数不会对年月日处理

     SELECT TIME_TO_SEC('24:00:00');

     结果：86400
     SELECT SEC_TO_TIME(86400);

     结果：24:00:00

9、拼凑日期、时间：MAKEDATE(year,dayofyear)，MAKETIME(hour,minute,second)

     SELECT MAKEDATE(2020,31);

     结果：2020-01-31
     SELECT MAKEDATE(2020,199);

     结果：2020-07-17
     SELECT MAKETIME(16,50,30);

     结果：16:50:30

10、日期计算（为日期增加一个时间间隔）：ADDDATE(date,INTERVAL expr unit)，ADDDATE(expr,days)

       SELECT ADDDATE('2020-01-06 17:19:00',INTERVAL 1 day)

       结果：2020-01-07 17:19:00

       SELECT ADDDATE(NOW(),INTERVAL 1 day)

       结果：2020-01-07 17:19:00

       SELECT ADDDATE('2020-01-06 17:19:00',10);

       结果：2020-01-16 17:19:00

11、时间计算（为时间增加一个时间间隔）：ADDTIME(expr1,expr2)

       SELECT ADDTIME('2020-01-06 17:19:00', '01:00:08');

       结果：2020-01-06 18:19:08

       SELECT ADDTIME('2020-01-06 17:19:00', '08');

       结果：2020-01-06 17:19:08

       SELECT ADDTIME('2020-01-06 17:19:00', '01:08');

       结果：2020-01-06 18:27:00

       SELECT ADDTIME('2020-01-06 17:19:00', 8);

       结果：2020-01-06 17:19:08

       SELECT ADDTIME('2020-01-06 17:19:00', '01:02:10');

       结果：2020-01-06 18:21:10

12、日期、时间计算（为日期、时间增加一个时间间隔）：DATE_ADD(date,INTERVAL expr unit)

        SELECT DATE_ADD('2020-01-06 17:19:00', INTERVAL 1 DAY);

       结果：2020-01-07 17:19:00

       SELECT DATE_ADD('2020-01-06 17:19:00', INTERVAL 1 YEAR);

       结果：2021-01-06 17:19:00

13、日期计算（为日期减去一个时间间隔）：DATE_SUB(date,INTERVAL expr unit)

       SELECT DATE_SUB('2020-01-08 10:02:00', INTERVAL 1 DAY);

       结果：2020-01-07 10:02:00

       SELECT DATE_SUB('2020-01-08 10:02:00', INTERVAL 1 MONTH);

       结果：2019-12-08 10:02:00

14、日期间隔天数计算：DATEDIFF(expr1,expr2)，返回两个日期之间的天数（expr1-expr2），只有值的日期部分参与计算

       SELECT DATEDIFF('2020-01-08', '2020-01-01');

       结果：7

       SELECT DATEDIFF('2020-01-01', '2020-01-08');

       结果：-7

15、时间间隔计算：TIMEDIFF(expr1,expr2)，返回两个时间之间的时间间隔

      SELECT TIMEDIFF('15:40:10','10:10:01');

       结果：05:30:09

       SELECT TIMEDIFF('10:10:01', '15:40:10');

       结果：-05:30:09

      SELECT TIMEDIFF('2020-01-08 10:00:00','10:00:00') ;

       结果：Null

       SELECT TIMEDIFF('2020-01-08',NULL);

       结果：Null

16、时间差计算：TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)，返回（datetime_expr2-datetime_expr1）的时间差

      SELECT TIMESTAMPDIFF(DAY, '2020-01-01', '2020-01-08');

       结果：7

       SELECT TIMESTAMPDIFF(DAY, '2020-01-08', '2020-01-01');

       结果：-7

17、时区转换：CONVERT_TZ(dt,from_tz,to_tz)

       SELECT CONVERT_TZ('2020-01-08 12:00:00', '+08:00', '+00:00');

       结果：2020-01-08 04:00:00

 

附录1：unit选项格式如下：

MICROSECOND

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR

SECOND_MICROSECOND

MINUTE_MICROSECOND

MINUTE_SECOND

HOUR_MICROSECOND

HOUR_SECOND

HOUR_MINUTE

DAY_MICROSECOND

DAY_SECOND

DAY_MINUTE

DAY_HOUR

YEAR_MONTH

---

附录2：format 规定日期/时间的输出格式如下：

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位