mysql 数字 日期函数 mysql的日期函数怎么用

日期和时间函数主要用来处理日期和时间值，一般的日期函数除了使用 DATE 类型的参数外， 也可以使用 DATETIME 或者 TIMESTAMP 类型的参数，但会忽略这些值的时间部分。相同的，以 TIME 类型值为参数的函数，可以接受 TIMESTAMP 类型的参数，但会忽略日期部分，许多日期函 数可以同时接受数字和字符串类型的两种参数，下面将介绍各种日期和时间函数的功能和用法。

1. 获取当前日期的函数和获取当前时间的函数 CURDATE()和 CURRENT_DATE()函数的作用相同，将当前日期按照‘YYYY-MM-DD’或 YYYYMMDD 格式的值返回，具体格式根据函数在字符串或是数字语境中而定 使用日期函数获取系统当前日期，输入语句如下：

SELECT CURDATE(),CURRENT_DATE(), CURDATE() + 0;

可以看到，两个函数作用相同，都返回了相同的系统当前日期，“CURDATE() + 0”将当前 日期值转换为数值型。

CURTIME()和 CURRENT_TIME()函数的作用相同，将当前时间以‘HH:MM:SS’或 HHMMSS 的格式返回，具体格式根据函数在字符串或是数字语境中而定。 使用时间函数获取系统当前时间，输入语句如下：

SELECT CURTIME(),CURRENT_TIME(),CURTIME() + 0;

可以看到，两个函数的作用相同，都返回了相同的系统当前时间，“CURTIME () + 0”将当 前时间值转换为数值型。

2. 获取当前日期和时间的函数 CURRENT_TIMESTAMP()、LOCALTIME()、NOW()和 SYSDATE() 4 个函数的作用相同，均 返回当前日期和时间值，格式为‘YYYY-MM-DD HH:MM:SS’或 YYYYMMDDHHMMSS，具体 格式根据函数在字符串或数字语境中而定。

SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();

可以看到，4 个函数返回的结果是相同的。

3. UNIX 时间戳函数 UNIX_TIMESTAMP(date)若无参数调用，则返回一个 UNIX 时间戳（‘1970-01-01 00:00:00’ GMT 之后的秒数）作为无符号整数。其中，GMT（Green wich mean time）为格林尼治标准时间。 若用 date 来调用 UNIX_TIMESTAMP()，它会将参数值以‘1970-01-01 00:00:00’GMT 后的秒数的 形式返回。date 可以是一个 DATE 字符串、DATETIME 字符串、TIMESTAMP 或一个当地时间的 YYMMDD 或 YYYYMMDD 格式的数字。 使用 UNIX_TIMESTAMP 函数返回 UNIX 格式的时间戳，输入语句如下：

SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()), NOW();

FROM_UNIXTIME(date)函数把 UNIX 时间戳转换为普通格式的时间，与 UNIX_TIMESTAMP (date)函数互为反函数。 使用 FROM_UNIXTIME 函数将 UNIX 时间戳转换为普通格式时间，输入语句如下：

SELECT FROM_UNIXTIME('1541844424');

可以看到，FROM_UNIXTIME('1541844424')与【例 6.56】中 UNIX_TIMESTAMP(NOW())的 结果正好相反，即两个函数互为反函数。

4. 返回 UTC 日期的函数和返回 UTC 时间的函数

SELECT UTC_DATE(), UTC_DATE() + 0;

UTC_DATE()函数返回值为当前时区的日期值。 UTC_TIME()返回当前 UTC 时间值，其格式为‘HH:MM:SS’或 HHMMSS，具体格式取决于 函数是否用在字符串或数字语境中。 使用 UTC_TIME()函数返回当前 UTC 时间值，输入语句如下：

SELECT UTC_TIME(), UTC_TIME() + 0;

UTC_TIME()返回当前时区的时间值

5. 获取月份的函数 MONTH(date)和 MONTHNAME(date)

SELECT MONTH('2020-02-13');

MONTHNAME(date)函数返回日期 date 对应月份的英文全名。 使用 MONTHNAME()函数返回指定日期中月份的名称，输入语句如下：

SELECT MONTHNAME('2018-02-13');

6. 获取星期的函数DAYNAME(d)、DAYOFWEEK(d)和WEEKDAY(d)

SELECT DAYNAME('2018-10-10');

可以看到，2018 年 10 月 10 日是星期三，因此返回结果为 Wednesday。 DAYOFWEEK(d)函数返回 d 对应的一周中的索引（位置，1 表示周日，2 表示周一，...，7 表 示周六）。 使用 DAYOFWEEK()函数返回日期对应的周索引，输入语句如下

SELECT DAYOFWEEK('2018-10-10');

上可知，2018 年 10 月 10 日为周三，因此返回其对应的索引值，结果为 4。 WEEKDAY(d)返回 d 对应的工作日索引：0 表示周一，1 表示周二，...，6 表示周日。 使用 WEEKDAY()函数返回日期对应的工作日索引，输入语句如下：

SELECT WEEKDAY('2018-10-10 22:23:00'), WEEKDAY('2018-11-11');

可以看到，WEEKDAY()和 DAYOFWEEK()函数都是返回指定日期在某一周内的位置，只是 索引编号不同

7. 获取星期数的函数 WEEK(d)和 WEEKOFYEAR(d)

使用 WEEK()函数查询指定日期是一年中的第几周，输入语句如下：

SELECT WEEK('2018-02-20'),WEEK('2018-02-20',0), WEEK('2018-02-20',1);

可以看到，WEEK('2018-02-20')使用一个参数，其第二个参数为 default_week_format 默认值， MySQL 中该值默认为 0，指定一周的第一天为周日，因此和 WEEK('2018-02-20',0)返回结果相同； WEEK('2018-02-20',1)中第二个参数为 1，指定一周的第一天为周一，返回值为 8。可以看到，第二 个参数不同，返回的结果也不同，使用不同的参数的原因是不同地区和国家的习惯不同，每周的第 一天并不相同。 WEEKOFYEAR(d)计算某天位于一年中的第几周，范围是 1~53，相当于 WEEK(d,3)。 使用 WEEKOFYEAR()查询指定日期是一年中的第几周，输入语句如下：

SELECT WEEK('2018-01-20',3), WEEKOFYEAR('2018-01-20');

可以看到，两个函数返回结果相同。

8. 获取天数的函数 DAYOFYEAR(d)和 DAYOFMONTH(d)

DAYOFYEAR(d)函数返回 d 是一年中的第几天，范围是 1~366。

使用 DAYOFYEAR()函数返回指定日期在一年中的位置，输入语句如下：

SELECT DAYOFYEAR('2018-02-20');

1 月份 31 天，再加上 2 月份的 20 天，因此返回结果为 51。

DAYOFMONTH(d)函数返回 d 是一个月中的第几天，范围是 1~31。 使用 DAYOFMONTH()函数返回指定日期在一个月中的位置，输入语句如下：

SELECT DAYOFMONTH('2018-08-20');

9. 获取年份、季度、小时、分钟和秒钟的函数 YEAR(date) 返回 date 对应的年份，范围是 1970~2069。 使用 YEAR()函数返回指定日期对应的年份，输入语句如下：

SELECT YEAR('18-02-03'),YEAR('96-02-03');

提 示 ：‘00~69’转换为‘2000~2069’，‘70~99’转换为‘1970~1999’。

QUARTER(date)返回 date 对应的一年中的季度值，范围是 1~4。 使用 QUARTER()函数返回指定日期对应的季度，输入语句如下

SELECT QUARTER('18-04-01');

MINUTE(time)返回 time 对应的分钟数，范围是 0~59。 使用 MINUTE()函数返回指定时间的分钟值，输入语句如下：

SELECT MINUTE('18-02-03 10:10:03');

SECOND(time)返回 time 对应的秒数，范围是 0~59。 使用 SECOND()函数返回指定时间的秒值，输入语句如下：

SELECT SECOND('10:05:03');

10. 获取日期的指定值的函数 EXTRACT(type FROM date) EXTRACT(type FROM date) 函数所使用的时间间 隔 类 型 说 明 符 与 DATE_ADD() 或 DATE_SUB()的相同，但它从日期中提取一部分，而不是执行日期运算。 使用 EXTRACT 函数提取日期或者时间值，输入语句如下：

SELECT EXTRACT(YEAR FROM '2018-07-02') AS col1,EXTRACT(YEAR_MONTH FROM '2018-07-12 01:02:03') AS col2,EXTRACT(DAY_MINUTE FROM '2018-07-12 01:02:03') AS col3;

type 值为 YEAR 时，只返回年值，结果为 2018；type 值为 YEAR_MONTH 时返回年与月份， 结果为 201807；type 值为 DAY_MINUTE 时，返回日、小时和分钟值，结果为 120102。

11. 时间和秒钟转换的函数 TIME_TO_SEC(time)返回已转化为秒的 time 参数。转换公式为：小时*3600+分钟*60+秒。 使用 TIME_TO_SEC 函数将时间值转换为秒值，输入语句如下：

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

SEC_TO_TIME(seconds)返回被转化为小时、分钟和秒数的 seconds 参数值，其格式为 ‘HH:MM:SS’或 HHMMSS，具体格式根据该函数是否用在字符串或数字语境中而定。 使用 SEC_TO_TIME()函数将秒值转换为时间格式，输入语句如下：

SELECT SEC_TO_TIME(2345),SEC_TO_TIME(2345)+0, TIME_TO_SEC('23:23:00'), SEC_TO_TIME(84180);

可以看到，SEC_TO_TIME 函数返回值加上 0 值之后变成了小数值；TIME_TO_SEC 正好和 SEC_TO_TIME 互为反函数。

12. 计算日期和时间的函数

若 date 参数是一个 DATE 值，计算只会包括 YEAR、MONTH 和 DAY 部分（没有时间部分）， 其结果是一个 DATE 值；否则，结果将是一个 DATETIME 值。 DATE_ADD(date,INTERVAL expr type)和 ADDDATE(date,INTERVAL expr type)两个函数的 作用相同，执行日期的加运算。 使用 DATE_ADD()和 ADDDATE()函数执行日期加操作，输入语句如下：

SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND) AS col1,ADDDATE('2010-12-31 23:59:59', INTERVAL 1 SECOND) AS col2, DATE_ADD('2010-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND) AS col3;

由结果 可 以 看 到 ， DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND) 和 ADDDATE('2010-12-31 23:59:59', INTERVAL 1 SECOND)两个函数执行的结果是相同的，将时间增 加 1 秒后返回，结果都为‘2011-01-01 00:00:00’；DATE_ADD('2010-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND)日期运算类型是 MINUTE_SECOND，将指定时间增加 1 分 1 秒后返回，结果 为‘2011-01-01 00:01:00’。

DATE_SUB(date,INTERVAL expr type)或者 SUBDATE(date,INTERVAL expr type)两个函数的 作用相同，执行日期的减运算。 使用 DATE_SUB 和 SUBDATE 函数执行日期减操作，输入语句如下：

SELECT DATE_SUB('2011-01-02', INTERVAL 31 DAY) AS col1, SUBDATE('2011-01-02', INTERVAL 31 DAY) AS col2,DATE_SUB('2011-01-01 00:01:00',INTERVAL '0 0:1:1' DAY_SECOND) AS col3;

由结果可以看到，DATE_SUB('2011-01-02', INTERVAL 31 DAY)和 SUBDATE('2011-01-02', INTERVAL 31 DAY)两个函数执行的结果是相同的，将日期值减少 31 天后返回，结果都为 “2010-12-02”；DATE_SUB('2011-01-01 00:01:00',INTERVAL '0 0:1:1' DAY_SECOND)函数将指定 日期减少 1 天，时间减少 1 分 1 秒后返回，结果为“2010-12-31 23:59:59”。

提 示 ：DATE_ADD 和 DATE_SUB 在指定修改的时间段时，也可以指定负值，负值代表相减， 即返回以前的日期和时间。

ADDTIME(date,expr)函数将 expr 值添加到 date，并返回修改后的值，date 是一个日期或者日 期时间表达式，而 expr 是一个时间表达式。 使用 ADDTIME 进行时间加操作，输入语句如下：

SELECT ADDTIME('2000-12-31 23:59:59','1:1:1'), ADDTIME('02:02:02',
'02:00:00');

可以看到，将“2000-12-31 23:59:59”的时间部分值增加 1 小时 1 分钟 1 秒后的日期变为 “2001-01-01 01:01:00”；“02:02:02”增加两小时后的时间为“04:02:02”。 SUBTIME(date,expr)函数将 date 减去 expr 值，并返回修改后的值。其中，date 是一个日期或 者日期时间表达式，而 expr 是一个时间表达式。 使用 SUBTIME()函数执行时间减操作，输入语句如下：

SELECT SUBTIME('2000-12-31 23:59:59','1:1:1'),SUBTIME('02:02:02','02:00:00');

可以看到，将“2000-12-31 23:59:59”的时间部分值减少 1 小时 1 分钟 1 秒后的日期变为 “2000-12-31 22:58:58”； “02:02:02”减少两小时的时间为“00:02:02”。

DATEDIFF(date1,date2)返回起始时间 date1 和结束时间 date2 之间的天数。date1 和 date2 为日 期或日期时间表达式。计算中只用到这些值的日期部分。 使用 DATEDIFF()函数计算两个日期之间的间隔天数，输入语句如下：

SELECT DATEDIFF('2010-12-31 23:59:59','2010-12-30') AS col1,DATEDIFF('2010-11-30 23:59:59','2010-12-31') AS col2;

DATEDIFF()函数返回 date1-date2 后的值，因此 DATEDIFF('2010-12-31 23:59:59','2010-12-30') 返回值为 1；DATEDIFF('2010-11-30 23:59:59','2010-12-31')返回值为-31。

13. 将日期和时间格式化的函数

使用 DATE_FORMAT()函数格式化输出日期和时间值，输入语句如下：

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') AS col1,
DATE_FORMAT('1997-10-04 22:23:00','%D %y %a %d %m %b %j') AS col2;

可以看到“1997-10-04 22:23:00”分别按照不同参数转换成了不同格式的日期值和时间值。

TIME_FORMAT(time,format)根据表达式 format 的要求显示时间 time。表达式 format 指定了显 示的格式。因为 TIME_FORMAT(time,format)只处理时间，所以 format 只使用时间格式。 使用 TIME_FORMAT()函数格式化输入时间值，输入语句如下：

SELECT TIME_FORMAT('16:00:00', '%H %k %h %I %l');

TIME_FORMAT 只处理时间值，可以看到，“16:00:00”按照不同的参数转换为不同格式的 时间值。

GET_FORMAT(val_type, format_type)返回日期时间字符串的显示格式，val_type 表示日期数据 类型，包括 DATE、DATETIME 和 TIME；format_type 表示格式化显示类型，包括 EUR、INTERVAL、 ISO、JIS、USA。GET_FORMAT 根据两个值类型组合返回的字符串显示格式如下表 所示。

使用 GET_FORMAT()函数显示不同格式化类型下的格式字符串，输入语句如下：

SELECT GET_FORMAT(DATE,'EUR'), GET_FORMAT(DATE,'USA');

可以看到，不同类型的格式化字符串并不相同。 在 DATE_FORMAT()函数中，使用 GET_FORMAT 函数返回的显示格式字符串来 显示指定的日期值，输入语句如下：

SELECT DATE_FORMAT('2000-10-05 22:23:00', GET_FORMAT(DATE,'USA') );

GET_FORMAT(DATE,'USA')返回的显示格式字符串为%m.%d.%Y，对照表 6.3 中的显示格式 （%m 以数字形式显示月份，%d 以数字形式显示日，%Y 以 4 位数字形式显示年），因此结果为 10.05.2000。