MySQL日期和时间函数

﹏ヽ暗。殇╰゛Y 2022-05-18 09:00 370阅读 0赞

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选项

![week()函数的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] #得到指定日期是一年的第几天

[week_model]: /images/20220518/7e797fd31a8f4ed4acce468667cf73c5.png