MySQL日期和时间函数

1. 获取一个日期时间的部分数据

SELECT DATE('2018-11-01 13:12:41');    -- [2018-11-01]  #获取日期部分
SELECT TIME('2018-11-01 13:12:41');    -- [13:12:41]    #获取时间部分
SELECT YEAR('2018-11-01 13:12:41');    -- [2018]        #获取年
SELECT MONTH('2018-11-01 13:12:41');   -- [11]          #获取月
SELECT DAY('2018-11-01 13:12:41');     -- [1]           #获取天
SELECT HOUR('2018-11-01 13:12:41');    -- [13]          #获取时
SELECT MINUTE('2018-11-01 13:12:41');  -- [12]          #获取分
SELECT SECOND('2018-11-01 13:12:41');  -- [41]          #获取秒

SELECT QUARTER('2018-08-02');    -- [3]     #获取季节（1,2,3,4）->（1~3,4~6,7~9,10~12）

SELECT WEEK('2018-02-01',0);     -- [4]
        -- 一年中的第几个星期，要根据具体需求设置后面的 model 参数，model 不同得到的结果会有差异

week()函数的model选项

2. 查询当前日期和时间

SELECT CURDATE()/CURRENT_DATE()/CURRENT_DATE;
    -- [2018-08-01]          #获取当前日期。这三种写法是同义的，下同
SELECT CURTIME()/CURRENT_TIME()/CURRENT_TIME;
    -- [12:41:55]          #获取当前时间
SELECT NOW()/CURRENT_TIMESTAMP()/CURRENT_TIMESTAMP;
    -- [2018-08-01 12:42:19]    #获取当前日期 + 时间

3. 增减 年、月、日、时、分、秒

SELECT DATE_ADD('2018-08-01', INTERVAL 1 YEAR);      -- [2019-08-01]      #年份+1
SELECT DATE_ADD('2018-12-01', INTERVAL 1 MONTH);     -- [2019-01-01]      #月份+1
SELECT DATE_ADD('2018-08-01', INTERVAL 1 DAY);       -- [2018-08-02]      #天数+1
SELECT DATE_ADD('2018-08-01', INTERVAL -1 DAY);      -- [2018-07-31]      #天数-1
SELECT DATE_ADD('2018-08-01 13:12:41', INTERVAL 1 HOUR);   -- [2018-08-01 14:12:41] #小时+1
SELECT DATE_ADD('2018-08-01 13:12:41', INTERVAL 1 MINUTE); -- [2018-08-01 13:13:41] #分钟+1
SELECT DATE_ADD('2018-08-01 13:12:41', INTERVAL 1 SECOND); -- [2018-08-01 13:12:42] #秒数+1
SELECT DATE_ADD('2018-08-01', INTERVAL 1 WEEK);          -- [2018-08-08] #增加一个星期（7天）

SELECT DATE_ADD('2018-08-01',INTERVAL 1 YEAR_MONTH);     -- [2018-09-01] #增加一年零一个月
    -- DAY_HOUR       : 日和小时
    -- DAY_MINUTE     : 日和分钟
    -- DAY_SECOND     : 日和秒
    -- HOUR_MINUTE    : 小时和分
    -- HOUR_SECOND    : 小时和秒
    -- MINUTE_SECOND  : 分和秒
    
SELECT DATE_SUB('2018-08-01', INTERVAL 1 YEAR);
    -- [2017-08-01]   #date_sub()与date_add()相反

3. 计算日期间隔

SELECT DATEDIFF('2018-08-14', '2018-08-01');   -- [13]   #计算两个日期间隔的天数
SELECT DATEDIFF('2018-08-01', '2018-08-14');   -- [-13]  #计算两个日期间隔的天数

SELECT TIMEDIFF('2018-08-01 01:30:00', '2018-08-01 01:00:00');
    -- [00:30:00]     #计算两个时间间隔，两个参数格式必须要一样
SELECT TIMEDIFF('2018-08-01 01:00:00', '2018-08-01 01:30:00');
    -- [-00:30:00]     #计算两个时间间隔，两个参数格式必须要一样

4. 字符串转换为日期

SELECT STR_TO_DATE('11/09/2014', '%m/%d/%Y');
    -- [2014-11-09]    #字符串的日期间隔符要和后面的一样
SELECT STR_TO_DATE('11-09-2014', '%m-%d-%Y');
    -- [2014-11-09]
SELECT STR_TO_DATE('2014$11$09', '%Y$%m$%d');
    -- [2014-11-09]    #用其他间隔符也可以，但前后要统一
SELECT STR_TO_DATE('13:12:41', '%H:%i:%s');
    -- [13:12:41]
SELECT STR_TO_DATE('2014-09-11 13:12:41', '%Y-%m-%d %H:%i:%s');
    -- [2014-09-11 13:12:41] #分钟用i，不要用m

5. 日期转换为字符串

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H时%i分%s秒');  -- [2018年08月01日 15时26分39秒]
SELECT TIME_FORMAT(CURTIME(),'%H时%i分%s秒');          -- [15时28分03秒]    #只能转换时间部分

6. 时间和秒数的互转

SELECT TIME_TO_SEC('00:30:00');  -- [1800]       #时间转为秒
SELECT SEC_TO_TIME(1801);        -- [00:30:01]   #秒转时间

7. 指定日期到 0000-01-01 过去了多少天

SELECT TO_DAYS('0000-01-01');    -- [1]
SELECT TO_DAYS('2018-01-02');    -- [737061]
SELECT FROM_DAYS(0);             -- [0000-00-00]
SELECT FROM_DAYS(737062);        -- [2018-01-03]

8.时间戳与日期互转

-- 时间戳是指格林威治时间1970年01月01日00时00分00秒
-- (北京时间1970年01月01日08时00分00秒)起至现在的总秒数
SELECT UNIX_TIMESTAMP();                        -- [1533109596]
SELECT UNIX_TIMESTAMP('1970-01-01 08:00:01');   -- [1]
SELECT FROM_UNIXTIME(1);                        -- [1970-01-01 08:00:01]
SELECT FROM_UNIXTIME(1,'%Y年%m月%d日 %H时%i分%s秒');    -- [1970年01月01日 08时00分01秒]

9. 拼凑日期，时间

SELECT MAKEDATE(2018,02);    -- [2018-01-02]
SELECT MAKEDATE(2018,32);    -- [2018-02-01]
SELECT MAKETIME(17,30,01);   -- [17:30:01]

10. 个性化显示时间日期

SELECT DAYOFWEEK('2018-08-01');   -- [4]   #得到指定日期是一个星期的第几天，注意：星期天是第1天
SELECT DAYOFMONTH('2018-08-05');  -- [5]   #得到指定日期是一个月的第几天
SELECT DAYOFYEAR('2018-08-01');   -- [213] #得到指定日期是一年的第几天