mysql 日期时间函数

绝地灬酷狼 2024-04-20 22:18 113阅读 0赞

学习了[字符串函数][Link 1]，接着学习日期时间函数，

### 3，日期和时间函数 ###

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

日期和时间函数表

<table> 
 <tbody> 
 <tr> 
 <td colspan="1"> 函数 </td> 
 <td colspan="1"> 说明 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> CURDATE()、 CURRENT_DATE() </td> 
 <td colspan="1"> 获取当前日期 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> CURTIME()、 CURRENT_TIME() </td> 
 <td colspan="1"> 获取当前时间 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> CURRENT_TIMESTAMP()、LOCALTIME()、 NOW()、 SYSDATE() </td> 
 <td colspan="1"> 获取当前日期和时间&nbsp; </td> 
 </tr> 
 <tr> 
 <td colspan="1"> UNIX_TIMESTAMP(date) </td> 
 <td colspan="1"> 获取时间戳 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> FROM_UNIXTIME(date) </td> 
 <td colspan="1"> 把UNIX时间戳转换为普通格式的时间&nbsp; </td> 
 </tr> 
 <tr> 
 <td colspan="1"> UTC_DATE() </td> 
 <td colspan="1"> 函数返回当前UTC日期值 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> UTC_TIME() </td> 
 <td colspan="1"> 返回当前UTC时间值 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> MONTH(date) </td> 
 <td colspan="1"> 返回date对应的月份，范围值从1~12 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> MONTHNAME(date) </td> 
 <td colspan="1"> 返回日期date对应月份的英文全名 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DAYNAME(d) </td> 
 <td colspan="1"> 返回d对应的工作日的英文名称，例如Sunday、Monday等 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DAYOFWEEK(d) </td> 
 <td colspan="1"> 返回d对应的一周中的索引(位置)。1表示周日，2表示周一，..7表示周六) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> WEEKDAY(d) </td> 
 <td colspan="1"> 返回d对应的工作日索引。0表示周一，1表示周二，....，6表示周日 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> WEEK(d) </td> 
 <td colspan="1"> 计算日期d是一年中的第几周 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> WEEKOFYEAR(d) </td> 
 <td colspan="1"> 计算某天位于一年中的第几周，范围是从1~53，相当于WEEK(d,3) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DAYOFYEAR(d) </td> 
 <td colspan="1"> 返回d是一年中的第几天，范围是从1~366 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DAYOFMONTH(d) </td> 
 <td colspan="1"> 返回d是一个月中的第几天，范围是从1~31 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> YEAR(date) </td> 
 <td colspan="1"> 返回date对应的年份,范围是1970~2069 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> QUARTER(date) </td> 
 <td colspan="1"> 返回date对应的一年中的季度值，范围是从1~4 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> MINUTE(time) </td> 
 <td colspan="1"> 返回time对应的分钟数,范围是从0~59 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> SECOND(time) </td> 
 <td colspan="1"> 返回time 对应的秒数。范围是从0~59 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> EXTRACT(type FROM date) </td> 
 <td colspan="1"> 从日期中提取一部分值 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> TIME_TO_SEC(time) </td> 
 <td colspan="1"> 返回已转化为秒的time参数。转换公式为：小时*3600+分钟*60+秒 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> SEC_TO_TIME(seconds) </td> 
 <td colspan="1"> 小返回被转化为小时、分钟和秒数的seconds参数值 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DATE_ADD(date, INTERVAL expr type)、ADDDATE(date, INTERVAL expr type) </td> 
 <td colspan="1"> 执行日期的加运算 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DATE_SUB(date, INTERVAL expr type)、SUBDATE(date, INTERVAL expr type) </td> 
 <td colspan="1"> 执行日期的减运算 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> ADDTIME(date,expr) </td> 
 <td colspan="1"> 将expr值添加到 date，并返回修改后的值，date是一个日期或者日期时间表达式，而expr是一个时间表达式 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> SUBTIME(date,expr) </td> 
 <td colspan="1"> 将date减去 expr值，并返回修改后的值，date是一个日期或者日期时间表达式，而expr是一个时间表达式 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DATEDIFF(date1,date2) </td> 
 <td colspan="1"> 返回起始时间datel和结束时间date2之间的天数 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> TIME_FORMAT(time,format) </td> 
 <td colspan="1"> 根据format字符串安排time值的格式 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> GET_FORMAT(val_type, format_type) </td> 
 <td colspan="1"> 回日期时间字符串的显示格式 </td> 
 </tr> 
 </tbody> 
</table>

#### 1，获取当前日期的函数和获取当前时间的函数 ####

CURDATE()和 CURRENT\_DATE()函数作用相同，将当前日期按照‘YYYY-MM-DD’或YYYYMMDD格式的值返回，具体格式根据函数在字符串或是数字语境中而定。

【例53】使用日期函数获取系统当前日期，输入语句如下：

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

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

CURTIME()和 CURRENT\_TIME()函数作用相同,将当前时间以‘HH：MM：SS'或HHMMSS的格式返回，具体格式根据函数在字符串或是数字语境中而定。

【例54】使用时间函数获取系统当前时间，输入语句如下：

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

![66273bf15acd4e8ead8220230138529a.png][]

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

#### 2，获取当前日期和时间的函数 ####

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

【例55】使用日期时间函数获取当前系统日期和时间，输入语句如下：

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

![da8cf7331b3a4f1fad1cfaadaa938065.png][]

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

#### 3，UNIX时间戳函数 ####

UNIX\_TIMESTAMP(date)若无参数调用,则返回一个Unix时间戳(‘1970-01-01 00：00：00’GMT之后的秒数〕作为无符号整数。其中，GMT (Greenwich mean time)为格林尼治标准时间)。若用date来调用UNIX\_TIMESTAMP()，它会将参数值以‘1970-01-01 00-00：0O’GMT后的秒数的形式返回。date可以是一个DATE字符串、DATETIME字符串、TIMESTAMP或一个当地时间的YYMMDD 或YYYYMMDD格式的数字。

【例56】使用UNIX\_TIMESTAMP函数返回UNIX格式的时间戳，输入语句如下：

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

![1f7f06ef89694782a09dfb407d2ae9c4.png][]

FROM\_UNIXTIME(date)函数把UNIX时间戳转换为普通格式的时间，与UNIX\_TIMESTAMP (date)函数互为反函数。

【例57】使用FROM\_UNIXTIME函数将UNIX时间截转换为普通格式时间，输入语句如下：

SELECT FROM_UNIXTIME('1466393937');

![fd4f4198829f4baa9f9e6d902b0d3db4.png][]

可以看到，FROM\_UNIXTIME("1466393937')与【例56】中 UNIX\_TIMESTAMP(NOWO)的结果正好相反，即两个函数互为反函数。

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

UTC\_DATE()函数返回当前UTC（世界标准时间〉日期值，其格式为‘YYYY-MM-DD’或YYYYMMDD，具体格式取决于函数是否用在字符串或数字语境中。

【例58】使用UTC\_DATEO函数返回当前UTC日期值，输入语句如下：

SELECT  UTC_DATE(), UTC_DATE()+0;

![68a4c070c6d34c6d84fab9623e045b41.png][]

UTC\_DATE()函数返回值为当前时区的日期值。

UTC\_TIME()返回当前UTC时间值，其格式为‘HH：MM：SS’或HHMMSS，具体格式取决于函数是否用在字符串或数字语境中。

【例59】使用UTC\_TIME()函数返回当前UTC时间值，输入语句如下：

SELECT UTC_TIME(), UTC_TIME() + 0;

![c8ab421d942c4e2385c8492ef1fcd2fd.png][]

UTC\_TIME()返回当前时区的时间值。

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

**MONTH(date)**函数返回date对应的月份，范围值从1~12。

【例60】使用MONTHO函数返回指定日期中的月份，输入语句如下：

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

![b83317f72dc0458489ba9eebd0891afd.png][]

**MONTHNAME(date)**函数返回日期date对应月份的英文全名。

【例61】使用MONTHNAMEO)函数返回指定日期中的月份的名称，输入语句如下：

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

![cae886b9802c47d889bb2252600939be.png][]

#### 6，获取星期的函数DAYNAME(d)DAYOFWEEK(d)和WEEKDAY(d) ####

**DAYNAME(d)**函数返回d对应的工作日的英文名称，例如Sunday、Monday等。

【例62】使用DAYNAME(函数返回指定日期的工作日名称，输入语句如下：

SELECT DAYNAME('2024-02-10');

![a45c28a833a441bfbb4117f764550b09.png][]

可以看到，2024年2月10日是星期六，因此返回结果为Saturday。

**DAYOFWEEK(d)**函数返回d对应的一周中的索引(位置)。1表示周日，2表示周一，..7表示周六)。

【例63】使用DAYOFWEEKO函数返回日期对应的周索引，输入语句如下：

SELECT DAYOFWEEK('2024-02-14');

![e49e7b9cc53143b5a3e25d61e60714b4.png][]

由【例63】可知，2024年2月14日为周三，因此返回其对应的索引值，结果为4。

**WEEKDAY(d)**返回d对应的工作日索引。0表示周一，1表示周二，....，6表示周日。

【例64】使用WEEKDAYO函数返回日期对应的工作日索引，输入语句如下：

SELECT WEEKDAY('2024-02-14 22：23：00'), WEEKDAY('2024-04-01');

![01fbbfb650d744bda3e58d3091f9fe96.png][]

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

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

WEEK(d)计算日期d是一年中的第几周。WEEK()的双参数形式允许指定该星期是否起始于周日或周一，以及返回值的范围是否为从0~53或从1~53。若 Mode参数被省略，则使用default\_week\_format系统自变量的值,可参考表1。

表1 WEEK函数中Mode参数取值

<table> 
 <tbody> 
 <tr> 
 <td colspan="1"> Mode </td> 
 <td colspan="1"> 一周的第一天 </td> 
 <td colspan="1"> 范围 </td> 
 <td colspan="1"> Week1为第一周 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> 0 </td> 
 <td colspan="1"> 周日 </td> 
 <td colspan="1"> 0~53 </td> 
 <td colspan="1"> 本年度中有一个周日 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> 1 </td> 
 <td colspan="1"> 周一 </td> 
 <td colspan="1"> 0~53 </td> 
 <td colspan="1"> 本年度中有3天以上 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> 2 </td> 
 <td colspan="1"> 周日 </td> 
 <td colspan="1"> 1~53 </td> 
 <td colspan="1"> 本年度中有一个周日 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> 3 </td> 
 <td colspan="1"> 周一 </td> 
 <td colspan="1"> 1~53 </td> 
 <td colspan="1"> 本年度中有3天以上 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> 4 </td> 
 <td colspan="1"> 周日 </td> 
 <td colspan="1"> 0~53 </td> 
 <td colspan="1"> 本年度中有3天以上 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> 5 </td> 
 <td colspan="1"> 周一 </td> 
 <td colspan="1"> 0~53 </td> 
 <td colspan="1"> 本年度中有一个周一 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> 6 </td> 
 <td colspan="1"> 周日 </td> 
 <td colspan="1"> 1~53 </td> 
 <td colspan="1"> 本年度中有3天以上 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> 7 </td> 
 <td colspan="1"> 周一 </td> 
 <td colspan="1"> 1~53 </td> 
 <td colspan="1"> 本年度中有一个周一 </td> 
 </tr> 
 </tbody> 
</table>

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

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

![8fe54612bf0840a08889fa1ad635ce05.png][]

可以看到，WEEK('2011-02-20')使用一个参数，其第二个参数为default\_week\_format 默认值，MySQL中该值默认为0，指定一周的第一天为周日，因此和WEEK(2011-02-20',0)返回结果相同；WEEK(2011-02-20',1)中第二个参数为1，指定一周的第一天为周一，返回值为7。可以看到，第二个参数的不同，返回的结果也不同，使用不同的参数的原因是不同地区和国家的习惯不同，每周的第一天并不相同。

**WEEKOFYEAR(d)**计算某天位于一年中的第几周，范围是从1~53。相当于WEEK(d,3)。

【例66】使用WEEKOFYEAR()查询指定日期是一年中的第几周，输入语句如下：

SELECT WEEK('2011-02-20',3), WEEKOFYEAR('2011-02-20');

![5ce497b1838b4d4db751e6bd42d50ce0.png][]

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

#### 7，获取天数的函数DAYOFYEAR(d)和 DAYOFMONTH(d) ####

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

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

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

![a3d8f44c1b674bc4b1cb3a6a92e7f076.png][]

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

**DAYOFMONTH(d)**函数返回d是一个月中的第几天，范围是从1~31。

【例68】使用DAYOFMONTH()函数返回指定日期在一个月中的位置，输入语句如

SELECT DAYOFMONTH('2016-02-20');

![a20bb18925e84d949ea6ccb51e668418.png][]

结果显而易见。

####  ####

#### 8，获取年份、季度、小时、分钟和秒钟的函数 ####

**YEAR(date)**返回date对应的年份,范围是1970~2069。

【例69】使用YEAR(函数返回指定日期对应的年份，输入语句如下：

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

![0d49f49a54a743159a9c91f6685a894a.png][]

提示： “00~69’转换为“2000-2069', ‘70-99’转换为‘1970~1999’。

**QUARTER(date)**返回date对应的一年中的季度值，范围是从1~4。

【例70】使用QUARTERO函数返回指定日期对应的季度，输入语句如下：

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

![26f4f3c3c12e410c8eb23943ecc4f373.png][]

**MINUTE(time)**返回time对应的分钟数,范围是从0~59。

【例71】使用MINUTE()函数返回指定时间的分钟值，输入语句如下：

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

![85a93f4f90694943abb0b4cb76bda1c4.png][]

**SECOND(time)**返回time 对应的秒数。范围是从0~59。

【例72】使用SECOND()函数返回指定时间的秒值，输入语句如下：

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

![00e43d6ce18c47db8c7c443f3a78a4a8.png][]

#### 9，获取日期的指定值的函数EXTRACT(type FROM date) ####

**EXTRACT(type FROM date)**函数所使用的时间间隔类型说明符同DATE\_ADD()或DATE\_SUB()的相同，但它从日期中提取一部分，而不是执行日期运算。

【例73】使用EXTRACT函数提取日期或者时间值，输入语句如下：

SELECT EXTRACT(YEAR FROM '2016-07-02') AS eol1,
    EXTRACT(YEAR_MONTH FROM '2016-07-12 01:02:03') AS co12,
    EXTRACT(DAY_MINUTE FROM '2016-07-12 01:02:03') AS eo13;

![feab22bc888449f29ba0d7b45c3cf641.png][]

type值为YEAR时，只返回年值，结果为2011；type值为YEAR\_MONTH时返回年与月份，结果为201107；type值为DAY\_MINUTE时，返回日、小时和分钟值，结果为120102。

**TIME\_TO\_SEC(time)**返回已转化为秒的time参数。转换公式为：小时\*3600+分钟\*60+秒。

【例74】使用TIME\_TO\_SEC函数将时间值转换为秒值，输入语句如下：

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

![7691cccb220e48a3a60d66c5eb9e8634.png][]

**SEC\_TO\_TIME(seconds)**返回被转化为小时、分钟和秒数的seconds参数值，其格式为‘HHMM:SS’或HHMMSS，具体格式根据该函数是否用在字符串或数字语境中而定。

【例75】使用SEC\_TO\_TIME()函数将秒值转换为时间格式，输入语句如下：

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

![b70928ac71564a95803fe04f663a0a73.png][]

可以看到，SEC\_TO\_TIME函数返回值加上0值之后变成了小数值，TIME\_TO\_SEC正好和SEC\_TO\_TIME互为反函数。

#### 10，计算日期和时间的函数 ####

计算日期和时间的函数有：DATE\_ADD()、ADDDATE()、DATE\_SUB()、SUBDATE()、ADDTIME()、SUBTIME()和 DATE\_DIFF()。

**DATE\_ADD(date, INTERVAL expr type)**和**DATE\_SUB(date,INTERVAL expr type)**，其中，date是一个DATETIME或DATE值，用来指定起始时间。expr是一个表达式，用来指定从起始日期添加或减去的时间间隔值。expr是一个字符串;对于负值的时间间隔，它可以以一个负号‘-’开头。type为关键词，它指示了表达式被解释的方式。表2显示了type和 expr参数的关系。

表2 MySQL中计算日期和时间的格式

<table> 
 <tbody> 
 <tr> 
 <td colspan="1"> type的值 </td> 
 <td colspan="1"> 预期的exprt的值 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> MICROSECOND </td> 
 <td colspan="1"> MICROSECONDS </td> 
 </tr> 
 <tr> 
 <td colspan="1"> SECOND </td> 
 <td colspan="1"> SECONDS </td> 
 </tr> 
 <tr> 
 <td colspan="1"> MINUTE </td> 
 <td colspan="1"> MINUTES </td> 
 </tr> 
 <tr> 
 <td colspan="1"> HOUR </td> 
 <td colspan="1"> HOURS </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DAY </td> 
 <td colspan="1"> DAYS </td> 
 </tr> 
 <tr> 
 <td colspan="1"> WEEK </td> 
 <td colspan="1"> WEEKS </td> 
 </tr> 
 <tr> 
 <td colspan="1"> MONTH </td> 
 <td colspan="1"> MONTHS </td> 
 </tr> 
 <tr> 
 <td colspan="1"> QUARTER </td> 
 <td colspan="1"> QUARTERS </td> 
 </tr> 
 <tr> 
 <td colspan="1"> YEAR </td> 
 <td colspan="1"> YEARS </td> 
 </tr> 
 <tr> 
 <td colspan="1"> SECOND_MICROSECOND </td> 
 <td colspan="1"> 'SECONDS.MICROSECONDS' </td> 
 </tr> 
 <tr> 
 <td colspan="1"> MINUTE_MICROSECOND </td> 
 <td colspan="1"> 'MINUTES.MICROSECONDS' </td> 
 </tr> 
 <tr> 
 <td colspan="1"> MINUTE_SECOND </td> 
 <td colspan="1"> 'MINUTES:SECONDS' </td> 
 </tr> 
 <tr> 
 <td colspan="1"> HOUR_MICROSECOND </td> 
 <td colspan="1"> 'HOURS.MICROSECONDS' </td> 
 </tr> 
 <tr> 
 <td colspan="1"> HOUR_SECOND </td> 
 <td colspan="1"> 'HOURS:MINUTES:SECONDS' </td> 
 </tr> 
 <tr> 
 <td colspan="1"> HOUR_MINUTE </td> 
 <td colspan="1"> HOURS:MINUTES </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DAY_MICROSECOND </td> 
 <td colspan="1"> 'DAYS.MICROSECONDS' </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DAY_SECOND </td> 
 <td colspan="1"> 'DAYS HOURS:MINUTES:SECONDS' </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DAY_MINUTE </td> 
 <td colspan="1"> 'DAYS HOURS:MINUTES' </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DAY_HOUR </td> 
 <td colspan="1"> 'DAYS HOURS' </td> 
 </tr> 
 <tr> 
 <td colspan="1"> YEAR_MONTH </td> 
 <td colspan="1"> 'YEARS-MONTHS' </td> 
 </tr> 
 </tbody> 
</table>

若date参数是一个DATE值，计算只会包括YEAR、MONTH和DAY部分（即没有时间部分），其结果是一个DATE值。否则，结果将是一个DATETIME值。

**DATE\_ADD(date, INTERVAL expr type)**和**ADDDATE(date, INTERVAL expr type)**两个函数作用相同，执行日期的加运算。

【例76】使用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 co13;

![099756ca42494868872da4406e82adfa.png][]

由结果可以看到，DATE\_ADD(2010-12-3123：59：59'，INTERVAL 1 SECOND)和ADDDATE(2010-12-3123：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)**两个函数作用相同，执行日期的减运算。

【例77】使用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;

![2a86f06d4215478f9f1413e8e498c5b4.png][]

由结果可以看到，DATE\_SUB(2011-01-02'，INTERVAL 31DAY)和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是一个时间表达式。

【例78】使用ADDTIME进行时间加操作，输入语句如下：

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

![8f59ec8707d642129961666b4f6d7ca4.png][]

可以看到，将“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是一个时间表达式。

【例79】使用SUBTIME(函数执行时间减操作,输入语句如下：

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

![e76f4a77de204fcabcb590151b1d13db.png][]

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

**DATEDIFF(date1,date2)**返回起始时间datel和结束时间date2之间的天数。date1和 date2为日期或date-and-time表达式。计算中只用到这些值的日期部分。

【例80】使用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 col23;

![446f9918e43d4ad1be6b085e887a01b3.png][]

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

#### 11，将日期和时间格式化的函数 ####

DATE\_FORMAT(date,format)根据format指定的格式显示date值。主要format格式如表3所示。

表3 DATE\_FORMAT时间日期格式

<table> 
 <tbody> 
 <tr> 
 <td colspan="1"> 说明符 </td> 
 <td colspan="1"> 说明 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %a </td> 
 <td colspan="1"> 工作日的缩写名称(Sun..Sat) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %b </td> 
 <td colspan="1"> 月份的缩写名称(Jan..Dec) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %c </td> 
 <td colspan="1"> 月份，数字形式(0..12) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %D </td> 
 <td colspan="1"> 带有英语后缀的该月日期(0th, lst, 2nd, 3rd, ..) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %d </td> 
 <td colspan="1"> 该月日期，数字形式(00..31) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %e </td> 
 <td colspan="1"> 该月日期，数字形式(0..31) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %f </td> 
 <td colspan="1"> 微秒（00000..999999） </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %H </td> 
 <td colspan="1"> 以2位数表示24小时(00..23) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %h, %I </td> 
 <td colspan="1"> 以2位数表示12小时(01..12) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %i </td> 
 <td colspan="1"> '分钟,数字形(00..59) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %j </td> 
 <td colspan="1"> 一年中的天数(001.366) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %k </td> 
 <td colspan="1"> 以24(20..23)小时表示时间 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %l </td> 
 <td colspan="1"> 以12(1..12)小时表示时间 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %M </td> 
 <td colspan="1"> 月份名称(Janury..Dccember) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %n </td> 
 <td colspan="1"> 月份，数字形式(00..12) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %p </td> 
 <td colspan="1"> 上午(AM)或下午(PM) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %r </td> 
 <td colspan="1"> 时间，12小时制(小时hh:分钟mm:秒数ss后加AM或PM) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %S, %s </td> 
 <td colspan="1"> 以2位数形式表示秒(00..59) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %T </td> 
 <td colspan="1"> 时间，24小时制. (小时hh:分钟mm:秒数ss) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %U </td> 
 <td colspan="1"> 周(0.53),其中周日为每周的第一天 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %u </td> 
 <td colspan="1"> 周(00..53)， 其中周一为每周的第一天 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %V </td> 
 <td colspan="1"> 周(01..53)， 其中周日为每周的第一天；和%X同时使用 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %v </td> 
 <td colspan="1"> 周(01..53)，其中周一为每周的第一天；和%x同时使用 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %W </td> 
 <td colspan="1"> 工作日名称(周日..周六) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %w </td> 
 <td colspan="1"> 一周中的每日(0=周日..6=周六) </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %X </td> 
 <td colspan="1"> 该周的年份，其中周日为每周的第-天；数字形式，4位数；和%V同时使用 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %x </td> 
 <td colspan="1"> 该周的年份，其中周一-为每周的第一天；数字形式，4位数；和%v同时使用 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %Y </td> 
 <td colspan="1"> 4位数形式表示年份 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %y </td> 
 <td colspan="1"> 2位数形式表示年份 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> %% </td> 
 <td colspan="1"> '%'文字字符 </td> 
 </tr> 
 </tbody> 
</table>

【例81】使用DATE\_FORMAT()函数格式化输出日期和时间值，输入语句如下：

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') AS co11,
    DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j') AS co12,
    DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s') AS co13,
    DATE_FORMAT('1999-01-01', '%X %V') AS col4;

![02cfe7447ada4b599c3c8ed79f1accf1.png][]

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

**TIME\_FORMAT(time,format)**根据 format字符串安排time值的格式。format字符串可能仅会处理包含小时、分钟和秒的格式说明符，其他说明符产生一个 NULL值或0。若time值包含一个大于23的小时部分，则%H和%k小时格式说明符会产生一个大于0..23的通常范围的值。

【例82】使用TIME\_FORMATO)函数格式化输入时间值，输入语句如下：

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

![391933cd95424dca8df66ac6d90063cb.png][]

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根据两个值类型组合返回的字符串显示格式如表4所示。

表4 GET\_FORMAT返回的格式字符串

<table> 
 <tbody> 
 <tr> 
 <td colspan="1"> 值类型 </td> 
 <td colspan="1"> 格式化类型 </td> 
 <td colspan="1"> 显示格式化字符串 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DATE </td> 
 <td colspan="1"> EUR </td> 
 <td colspan="1"> %d.%m.%Y </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DATE </td> 
 <td colspan="1"> INTERVAL </td> 
 <td colspan="1"> %Y%m%d </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DATE </td> 
 <td colspan="1"> ISO </td> 
 <td colspan="1"> %Y-%m-%d </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DATE </td> 
 <td colspan="1"> JIS </td> 
 <td colspan="1"> %Y-%m-%d </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DATE </td> 
 <td colspan="1"> USA </td> 
 <td colspan="1"> %m.%d.%Y </td> 
 </tr> 
 <tr> 
 <td colspan="1"> TIME </td> 
 <td colspan="1"> EUR </td> 
 <td colspan="1"> %H.%i.%s </td> 
 </tr> 
 <tr> 
 <td colspan="1"> TIME </td> 
 <td colspan="1"> INTERVAL </td> 
 <td colspan="1"> %H%i%s </td> 
 </tr> 
 <tr> 
 <td colspan="1"> TIME </td> 
 <td colspan="1"> ISO </td> 
 <td colspan="1"> %H:%i:%s </td> 
 </tr> 
 <tr> 
 <td colspan="1"> TIME </td> 
 <td colspan="1"> JIS </td> 
 <td colspan="1"> %H:%i:%s </td> 
 </tr> 
 <tr> 
 <td colspan="1"> TIME </td> 
 <td colspan="1"> USA </td> 
 <td colspan="1"> %h:%i:%s %p </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DATETIME </td> 
 <td colspan="1"> EUR </td> 
 <td colspan="1"> %Y-%m-%d %H.%i.%s </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DATETIME </td> 
 <td colspan="1"> INTERVAL </td> 
 <td colspan="1"> %Y%m%d%H%i%s </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DATETIME </td> 
 <td colspan="1"> ISO </td> 
 <td colspan="1"> %Y-%m-%d %H:%i:%s </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DATETIME </td> 
 <td colspan="1"> JIS </td> 
 <td colspan="1"> %Y-%m-%d %H:%i:%s </td> 
 </tr> 
 <tr> 
 <td colspan="1"> DATETIME </td> 
 <td colspan="1"> USA </td> 
 <td colspan="1"> %Y-%m-%d %H.%i.%s </td> 
 </tr> 
 </tbody> 
</table>

【例83】使用GET\_FORMAT()函数显示不同格式化类型下的格式字符串，输入语句如下：

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

![18f04b97eccc4107861fb151e6c17f71.png][]

可以看到，不同类型的格式化字符串并不相同。

【例84】在DATE\_FORMATO函数中，使用GET\_FORMAT函数返回的显示格式字符串来显示指定的日期值,输入语句如下：

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

![0905dcaff327477b935cdb6352587fee.png][]

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

#### 12，将日期和时间字符串转为日期和时间 ####

STR\_TO\_DATE('日期时间字符串', '格式') 把日期时间字符串转为时间

SELECT STR_TO_DATE('2024-02-28 12:15:16', '%Y-%m-%d %H:%i:%s') AS datetime1,
      STR_TO_DATE('2024-02-28', '%Y-%m-%d') AS date1,
      STR_TO_DATE('12:15:16', '%H:%i:%s') AS time1;

![ef4ab74cd31643e1b863388a41d101e5.png][]

### 总结： ###

日期时间函数中，获取当前时间的NOW()是首当其冲；其次把时间格式化，变成字符串DATE\_FORMAT()函数和把字符串时间变成时间的函数STR\_TO\_DATE()这两个是高频使用，接着是DATEDIFF(date1,date2)计算两个日期差函数。

上一篇： 《[mysql 字符串函数][Link 1]》

下一篇： 《[mysql 条件/系统/加密/其它函数][mysql]》

[Link 1]: https://blog.csdn.net/qq_35461948/article/details/136919740
[66273bf15acd4e8ead8220230138529a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/d301f79b92ff4cc3840af975cb550586.png
[da8cf7331b3a4f1fad1cfaadaa938065.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/4b06f8d5f0184d489ccf412c58a55374.png
[1f7f06ef89694782a09dfb407d2ae9c4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/c0c7b939bf0c471482dd78f8b9e4d320.png
[fd4f4198829f4baa9f9e6d902b0d3db4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/83f09a7876d14dcaa928ae92aa3555e0.png
[68a4c070c6d34c6d84fab9623e045b41.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/fbcdd766b5e44a999e4648f518f5c8b7.png
[c8ab421d942c4e2385c8492ef1fcd2fd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/c0dc2111d4024f8fa6788c8e329c8358.png
[b83317f72dc0458489ba9eebd0891afd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/0f4ecd9d5ad44a7681444a582a9b1203.png
[cae886b9802c47d889bb2252600939be.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/874694ed08704a778f9aea75b7c6b698.png
[a45c28a833a441bfbb4117f764550b09.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/9b95f45a2bcc49b2b8cbce2e0566111a.png
[e49e7b9cc53143b5a3e25d61e60714b4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/9897c4dcf3524010b136fd77b0ed8460.png
[01fbbfb650d744bda3e58d3091f9fe96.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/481a339b0a1f4070809a9de38074eda0.png
[8fe54612bf0840a08889fa1ad635ce05.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/40762a200edf483a928d38c619280ad5.png
[5ce497b1838b4d4db751e6bd42d50ce0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/bb27f6480bb1464dbdfd7a8fbcd293cf.png
[a3d8f44c1b674bc4b1cb3a6a92e7f076.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/c2e5e3a43f084a2bb17e9587ce0226f8.png
[a20bb18925e84d949ea6ccb51e668418.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/b75f4f5ca0ea4e72be91fd672f37f9b1.png
[0d49f49a54a743159a9c91f6685a894a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/b4e8f8c63c864968bd460c0bc6b9cc47.png
[26f4f3c3c12e410c8eb23943ecc4f373.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/f7587791da1b4d32856e7b3f5b0b135c.png
[85a93f4f90694943abb0b4cb76bda1c4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/67654a95462443a6964d14fa1da3839a.png
[00e43d6ce18c47db8c7c443f3a78a4a8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/f4f423cdc6af4d7084635a767876c335.png
[feab22bc888449f29ba0d7b45c3cf641.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/36d8ba2396f149028df32ce29c08f526.png
[7691cccb220e48a3a60d66c5eb9e8634.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/a39337dc9c464c158759b29ca713fc48.png
[b70928ac71564a95803fe04f663a0a73.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/a74a5ac0feef496b93d68525b585cae8.png
[099756ca42494868872da4406e82adfa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/35c72bf4cc05460395a02f8d8c2f9a76.png
[2a86f06d4215478f9f1413e8e498c5b4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/b49b57de2175443f8abc4b78241cd29a.png
[8f59ec8707d642129961666b4f6d7ca4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/5e45b5efc7c14c589cb5c193e5e4ab65.png
[e76f4a77de204fcabcb590151b1d13db.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/cd422dd0eda049f68e118056d06eb7e2.png
[446f9918e43d4ad1be6b085e887a01b3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/d7b6872f4b3f4112930613132b136cf4.png
[02cfe7447ada4b599c3c8ed79f1accf1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/c31248694c0d452f861aee03badb1e06.png
[391933cd95424dca8df66ac6d90063cb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/e8e33772b81f48f7a1bac455cf5e96e0.png
[18f04b97eccc4107861fb151e6c17f71.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/eae9d2c26d224770ba437efe22bedc40.png
[0905dcaff327477b935cdb6352587fee.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/c69c7d53454347ec982c8a829c2ee897.png
[ef4ab74cd31643e1b863388a41d101e5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/f57622de7b4a46bea453a57479f938ef.png
[mysql]: https://blog.csdn.net/qq_35461948/article/details/136937262