MySQL常用函数汇总——时间操作函数
- CURDATE和CURRENT_DATE 两个函数的作用相同，将当前日期按照“YYYY-MM-DD”或“YYYYMMDD”格式的值返回
mysql> SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE()+0; +------------+----------------+------------------+ | CURDATE() | CURRENT_DATE() | CURRENT_DATE()+0 | +------------+----------------+------------------+ | 2017-04-01 | 2017-04-01 | 20170401 | +------------+----------------+------------------+ 1 row in set (0.03 sec)
- CURTIME和CURRENT_TIME 两个函数的作用相同，将当前时间以“HH：MM：SS”或“HHMMSS”格式返回
mysql> SELECT CURTIME(),CURRENT_TIME(),CURRENT_TIME()+0; +-----------+----------------+------------------+ | CURTIME() | CURRENT_TIME() | CURRENT_TIME()+0 | +-----------+----------------+------------------+ | 19:39:51 | 19:39:51 | 193951 | +-----------+----------------+------------------+ 1 row in set (0.04 sec)
- NOW和SYSDATE 两个函数的作用相同，都是返回当前日期和时间值，格式为“YYYY-MM-DD HH：MM：SS”或“YYYYMMDDHHMMSS”
mysql> SELECT NOW(),SYSDATE(); +---------------------+---------------------+ | NOW() | SYSDATE() | +---------------------+---------------------+ | 2017-04-01 19:36:52 | 2017-04-01 19:36:52 | +---------------------+---------------------+ 1 row in set (0.04 sec)
- UNIX_TIMESTAMP 获取UNIX时间戳，返回一个以 UNIX 时间戳为基础的无符号整数
mysql> SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW(); +------------------+-----------------------+---------------------+ | UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | NOW() | +------------------+-----------------------+---------------------+ | 1551251270 | 1551251270 | 2019-02-27 15:07:50 | +------------------+-----------------------+---------------------+ 1 row in set (0.00 sec)
- FROM_UNIXTIME 将 UNIX 时间戳转换为时间格式
mysql> SELECT FROM_UNIXTIME(1150051270); +---------------------------+ | FROM_UNIXTIME(1150051270) | +---------------------------+ | 2006-06-12 02:41:10 | +---------------------------+ 1 row in set (0.00 sec)
- MONTH 获取指定日期中的月份
mysql> SELECT MONTH('2017-12-15'); +---------------------+ | MONTH('2017-12-15') | +---------------------+ | 12 | +---------------------+ 1 row in set (0.00 sec)
- MONTHNAME 获取指定日期中月份的英文名称
mysql> SELECT MONTHNAME('2017-12-15'); +-------------------------+ | MONTHNAME('2017-12-15') | +-------------------------+ | December | +-------------------------+ 1 row in set (0.00 sec)
- DAY 获取指定日期中的日期
mysql> SELECT DAY('2006-06-24'); +-------------------+ | DAY('2006-06-24') | +-------------------+ | 24 | +-------------------+ 1 row in set (0.00 sec)
- DAYNAME 获取指定曰期对应的星期几的英文名称
mysql> SELECT DAYNAME('2006-06-12'); +-----------------------+ | DAYNAME('2006-06-12') | +-----------------------+ | Monday | +-----------------------+ 1 row in set (0.00 sec)
- DAYOFWEEK 返回指定日期在一周中对应的索引位置。1 表示周日，2 表示周一，……，7 表示周六
mysql> SELECT DAYOFWEEK('2017-12-15'); +-------------------------+ | DAYOFWEEK('2017-12-15') | +-------------------------+ | 6 | +-------------------------+ 1 row in set (0.04 sec)
- WEEK 获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
mysql> SELECT WEEK('2018-10-25',1); +----------------------+ | WEEK('2018-10-25',1) | +----------------------+ | 43 | +----------------------+ 1 row in set (0.00 sec)
- DAYOFYEAR 获取指定曰期是一年中的第几天，返回值范围是1~366
mysql> SELECT DAYOFYEAR('2017-12-15'); +-------------------------+ | DAYOFYEAR('2017-12-15') | +-------------------------+ | 349 | +-------------------------+ 1 row in set (0.00 sec)
- DAYOFMONTH 获取指定日期是一个月中是第几天，返回值范围是1~31
mysql> SELECT DAYOFMONTH('2017-12-15'); +--------------------------+ | DAYOFMONTH('2017-12-15') | +--------------------------+ | 15 | +--------------------------+ 1 row in set (0.02 sec)
- YEAR 从指定日期值中获取年份
mysql> SELECT YEAR(NOW()); +-------------+ | YEAR(NOW()) | +-------------+ | 2019 | +-------------+ 1 row in set (0.00 sec)
- TIME_TO_SEC 将时间转换为秒数
mysql> SELECT TIME_TO_SEC('15:15:15'); +-------------------------+ | TIME_TO_SEC('15:15:15') | +-------------------------+ | 54915 | +-------------------------+ 1 row in set (0.00 sec)
- SEC_TO_TIME 将秒数转换为时间
mysql> SELECT SEC_TO_TIME('54925'); +----------------------+ | SEC_TO_TIME('54925') | +----------------------+ | 15:15:25 | +----------------------+ 1 row in set (0.00 sec)
- DATE_ADD和ADDDATE 两个函数的作用相同，都用于执行日期的加运算
mysql> SELECT DATE_ADD('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C1, -> DATE_ADD('2018-10-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND) AS C2, -> ADDDATE('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C3; +---------------------+---------------------+---------------------+ | C1 | C2 | C3 | +---------------------+---------------------+---------------------+ | 2018-11-01 00:00:00 | 2018-11-01 00:01:00 | 2018-11-01 00:00:00 | +---------------------+---------------------+---------------------+ 1 row in set (0.00 sec)
- DATE_SUB和SUBDATE 两个函数的作用相同，都用于执行日期的减运算
mysql> SELECT DATE_SUB('2018-01-02',INTERVAL 31 DAY) AS C1, -> SUBDATE('2018-01-02',INTERVAL 31 DAY) AS C2, -> DATE_SUB('2018-01-01 00:01:00',INTERVAL '0 0:1:1' DAY_SECOND) AS C3; +------------+------------+---------------------+ | C1 | C2 | C3 | +------------+------------+---------------------+ | 2017-12-02 | 2017-12-02 | 2017-12-31 23:59:59 | +------------+------------+---------------------+ 1 row in set (0.00 sec)
- ADDTIME 用于执行时间的加运算
mysql> SELECT ADDTIME('2018-10-31 23:59:59','0:1:1'), -> ADDTIME('10:30:59','5:10:37'); +----------------------------------------+-------------------------------+ | ADDTIME('2018-10-31 23:59:59','0:1:1') | ADDTIME('10:30:59','5:10:37') | +----------------------------------------+-------------------------------+ | 2018-11-01 00:01:00 | 15:41:36 | +----------------------------------------+-------------------------------+ 1 row in set (0.00 sec)
- SUBTIME 用于执行时间的减运算
mysql> SELECT SUBTIME('2018-10-31 23:59:59','0:1:1'),SUBTIME('10:30:59','5:12:37'); +----------------------------------------+-------------------------------+ | SUBTIME('2018-10-31 23:59:59','0:1:1') | SUBTIME('10:30:59','5:12:37') | +----------------------------------------+-------------------------------+ | 2018-10-31 23:58:58 | 05:18:22 | +----------------------------------------+-------------------------------+ 1 row in set (0.00 sec)
- DATEDIFF 获取两个日期之间间隔，返回参数 1 减去参数 2 的值
mysql> SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1, -> DATEDIFF('2017-11-30','2017-12-15') AS col2; +------+------+ | COL1 | col2 | +------+------+ | 1 | -15 | +------+------+ 1 row in set (0.00 sec)
- DATE_FORMAT 格式化指定日期
mysql> SELECT DATE_FORMAT('2017-11-15 21:45:00','%W %M %D %Y') AS col1, -> DATE_FORMAT('2017-11-15 21:45:00','%h:i% %p %M %D %Y') AS col2; +------------------------------+----------------------------+ | col1 | col2 | +------------------------------+----------------------------+ | Wednesday November 15th 2017 | 09:i PM November 15th 2017 | +------------------------------+----------------------------+ 1 row in set (0.03 sec)
欢迎转载，转载时请注明来源。
