mysql 当前日期加3天_MySQL应用总结(十三)—函数的操作(3):日期时间函数

SQL语法预览：

使用函数：【select 函数名(参数1,参数2…);】

详解：

接上期...

四、 日期和时间函数

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

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

CURDATE()函数和CURRENT_DATE()函数的作用相同，将当前日期按照按照YYYY-MM-DD或YYYYMMDD格式返回。

CURTIME()函数和CURRENT_TIME()函数的作用相同，将当前时间以HH:MM:SS或HHMMSS格式返回。

案例：使用日期函数获取系统当前日期；使用时间函数获取当前时间，SQL语句如下：

命令语句：select CURDATE(),CURRENT_DATE(),CURDATE()+0;

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

执行结果：

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

可以看到，两个函数的作用相同，都返回了相同的系统当前时间。

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

CURRENT_TIMESTAMP()、LOCALTIME()、NOW()和SYSDATE()四个函数的作用相同。均返回当前日期和时间值，格式为YYYY-MM-DD HH:MM:SS或YYYYMMDDHHMMSS。

案例：使用日期函数获取当前系统日期和时间，SQL语句如下：

命令语句：selectCURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();

执行结果：

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

3. UNIX时间戳函数

UNIX_TIMESTAMP()函数以UNIX时间戳函数的形式返回当前时间；UNIX_TIMESTAMP(d)函数将时间d以UNIX时间戳的形式返回。

FROM_UNIXTIME(date)函数把UNIX时间戳函数转换为普通格式的时间，与UNIX_TIMESTAMP(date)函数互为反函数。

案例：使用UNIX_TIMESTAMP()函数返回UNIX格式的时间戳；使用FROM_UNIXTIME()函数将UNIX时间戳函数转换为普通格式时间，SQL语句如下：

命令语句：select UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW(), FROM_UNIXTIME(1534058518);

执行结果：

可以看到，FROM_UNIXTIME()函数与UNIX_TIMESTAMP()的结果正好相反，即两个函数互为反函数。

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

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

UTC_TIME()返回当前的UTC时间值，其格式为HH:MM:SS或HHMMSS，具体格式取决函数是在字符串还是数字语境中。

案例：使用UTC_DATE()函数返回当前UTC日期值；使用UTC_TIME返回当前的UTC时间值，SQL语句如下：

命令语句：select UTC_DATE(), UTC_DATE()+0;

select UTC_TIME(), UTC_TIME()+0;

执行结果：

UTC_DATE()函数的返回值为当前时区的日期值。UTC_TIME()返回当前时区的时间值。

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

MONTH(date)函数返回date对应月份，范围1—12。

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

案例：使用MONTH()函数指定日期中月份；使用MONTHNAME()函数返回指定日期中的月份名称，SQL语句如下：

命令语句：select MONTH(‘2018-08-12’), MONTH(‘2020-08-12’);

select MONTHNAME(‘2018-08-12’),MONTHNAME(‘2018-09-01’);

执行结果：

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

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

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

案例：使用DAYNAME()函数返回指定日期的星期数；使用DAYOFWEEK()函数返回当前日期对应的周索引，SQL语句如下：

命令语句：select DAYNAME(‘2018-08-12’), DAYOFWEEK(‘2018-08-12’);

执行结果：

由于2018年8月12日为周日，因此WEEKDAY()返回的结果是Sunday 而DAYOFWEEK()返回的索引值是1。可以看到，WEEKDAY()函数和DAYOFWEEK()函数都是返回指定日期在某一周内的位置，只是索引编号不同。

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

WEEK(d)函数用于计算日期d是一年中的第几周。WEEK()的双参数形式允许指定该星期是否起始于周日或周一，以及返回值是否为0-53或1-53.若Mode参数被省略，怎使用default_week_format系统自变量的值，可参考下表：

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

案例：使用WEEK()和WEEKOFYEAR()函数查询指定日期是一年中的第几周，SQL语句如下：

命令语句：select WEEK(‘2018-08-12’),WEEK(‘2018-08-12’,0),WEEK(‘2018-08-12’,1);

select WEEK(‘2018-08-12’,3),WEEKOFYEAR(‘2018-08-12’);

执行结果：

可以看到，WEEK(‘2018-08-12’)使用了一个参数，第二个参数为默认值default_week_format，MySQL中该值默认为0，指定一周的第一天为周日，因此和WEEK(‘2018-08-12’,0)的返回结果相同；另外WEEK(‘2018-08-12’,1)和WEEK(‘2018-08-12’,3)结果相同，说明2018年1月1日是个周日或周一，一周没有过3天，所以这几个参数结果都说明这周是第32周。

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

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

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

案例：使用DAYOFYEAR()函数返回指定日期在一年中的位置；使用DAYOFMONTH()函数返回指定日期在一个月中的位置，SQL语句如下：

命令语句：select DAYOFYEAR(‘2018-08-12’),DAYOFMONTH(‘2018-08-12’)

执行结果：

9. 获取年份、季度、小时、分钟和秒钟的函数

YEAR(date)函数返回date对应的年份，范围是1970-2069。

QUARTER(date)函数返回date对应的一年中的季度值，范围1-4。

HOUR(time)函数返回time对应的时钟数，范围是0-23。

MINUTE(time)函数返回time对应的分钟数，范围是0-59。

SECOND(time)函数返回time对应的秒钟数，范围是0-59。

案例：使用YEAR()函数返回指定日期对应的年份；使用QUARTER()函数返回指定日期对应的季度，使用HOUR()函数返回指定时间对应的时钟值，使用MINUTE()函数返回指定时间对应的分钟值，使用SECOND()函数返回指定时间对应的秒钟值，SQL语句如下：

命令语句：select YEAR(‘2018-08-12’),QUARTER(‘2018-08-12’),HOUR(‘2018-08-12 16:50:30’);

select MINUTE(‘2018-08-12 16:50:30’),SECOND(‘2018-08-12 16:50:30’);

执行结果：

10. 获取日期指定值的函数EXTRACT(type FROM date)

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

命令语句：select EXTRACT(YEAR FROM ‘2018-08-12’) as col1, EXTRACT(YEAR_MONTH FROM ‘2018-08-12’) as col2,EXTRACT(DAY_MINUTE FROM ‘2018-08-12 17:01:10’) as col3;

执行结果：

Type值为YEAR时只返回年值，结果为2018；type值为YEAR_MONTH时返回年月值，结果为201808；type值为DAY_MINUTE时返回日、小时和分钟值，结果为121701。

11. 时间和秒钟转换函数

TIME_TO_SEC(time)函数返回已转换为秒的time参数。转换公式为“小时*3600+分钟*60+秒”。

SEC_TO_TIME(seconds)函数返回时间，格式为HH:MM:SS或HHMMSS。

案例：使用TIME_TO_SEC()函数将时间转换为秒值；使用SEC_TO_TIME()函数将秒数转换为时间格式，SQL语句如下：

命令语句：select TIME_TO_SEC(‘2018-08-12 17:41:50’);

select SEC_TO_TIME(‘63710’);

执行结果：

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

计算日期时间的函数有DATE_ADD()、ADDDATE()、DATE_SUB()、SUBDATE()、ADDTIME()、SUBTIME()和DATE_DIFF()。

1).DATE_ADD(date,IINTERVAL expr type)和DATE_SUB(date,IINTERVAL expr type)

在DATE_ADD(date,IINTERVAL expr type)和DATE_SUB(date,IINTERVAL expr type)中，date是一个DATETIME或DATE值，用来指定起始时间；expr是一个表达式，用来指定从起始日期，开始添加或减去的时间间隔值，expr也是一个字符串，对于赋值的时间间隔，可以以一个负号开头；type为关键词，它表明了表达式被解释的方式。下表列出了type和expr参数的关系。

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

2) DATE_ADD(date,INTERVAL expr type)和ADDDATE(date,INTERVAL expr type)

DATE_ADD(date,INTERVAL expr type)和ADDDATE(date,INTERVAL expr type)两个函数的作用相同，都是执行日期的加运算。

案例：使用DATE_ADD()和ADDDATE()函数执行日期加操作，SQL语句如下：

命令语句：select DATE_ADD(‘2018-08-12 23:59:59’, INTERVAL 2 SECOND) AS col1, ADDDATE(‘2018-08-12 23:59:59’, INTERVAL 2 SECOND) AS col2, DATE_ADD(‘2018-08-12 23:59:59’, INTERVAL ‘1:1’ MINUTE_SECOND) AS col3;

执行结果：

3) DATE_SUB(date,INTERVAL expr type)和SUBDATE(date,INTERVAL expr type)

DATE_SUB(date,INTERVAL expr type)和SUBDATE(date,INTERVAL expr type)两个函数的作用相同，都是执行日期的减运算。

案例：使用DATE_SUB()和SUBDATE()函数执行日期减操作，SQL语句如下：

命令语句： select DATE_SUB(‘2019-01-01 00:00:01’, INTERVAL 2 SECOND)AS col1, SUBDATE(‘2019-01-01 00:00:01’, INTERVAL 2 SECOND) AS col2, DATE_SUB(‘2019-01-01 00:00:01’, INTERVAL ‘0 0:1:1’ DAY_SECOND) AS col3;

执行结果：

4) ADDTIME(date expr)函数

ADDTIME(date expr)函数将expr值添加到date，并返回修改后的值，date是一个日期或者日期表达式，而expr是一个时间表达式。

案例：使用ADDTIME()函数进行时间加操作，SQL语句如下：

命令语句：select ADDTIME(‘2018-12-31 23:59:59’,’’1:1:1’), ADDTIME(’01:01:01’, ’01:01:01’);

执行结果：

5) SUBTIME(date expr)函数

SUBTIME(date expr)函数将expr值减去到date，并返回修改后的值，date是一个日期或者日期表达式，而expr是一个时间表达式。

案例：使用SUBTIME()函数进行时间减操作，SQL语句如下：

命令语句： select SUBTIME(‘2018-12-31 23:59:59’,’’1:1:1’), SUBTIME(’01:01:01’, ’01:01:01’);

执行结果：

6) DATEDIFF(date1,date2)函数

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

案例：使用DATEDIFF()函数计算两个日期之间间隔的天数，SQL语句如下：

命令语句：select DATEDIFF(‘2018-12-31 23:59:59’,’2018-01-01’), DATEDIFF(‘2018-12-31 23:59:59’,’2019-01-01’);

执行结果：

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

1) DATE_FORMAT(date,format)函数。

DATE_FORMAT(date,format)函数根据format指定的格式显示date值。主要的format格式如下表所示

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

命令语句：select DATE_FORMAT(‘2018-01-01 10:10:10’,’%W %M %Y’) AS col1, DATE_FORMAT(‘2018-01-01 10:10:10’,’%D %y %a %b %m %b %j’) AS col2, DATE_FORMAT(‘2018-01-01 10:10:10’,’%H %i %s’) AS col3, DATE_FORMAT(‘2018-01-01 10:10:10’,’%X %V’) AS col4;

执行结果：

2) TIME_FORMAT(time,format)函数

TIME_FORMAT(time,format)函数根据format字符串安排time值的格式。若time值包含一个大于23的小时部分，%H和%K小时格式说明会产生一个超出0—23的通常范围的值。

案例：使用TIME_FORMAT()函数格式化输出时间值，SQL语句如下：

命令语句：select TIME_FORMAT(’20:00:00’,’%H %k %h %I %l’);

执行结果：

3) GET_FORMAT(val_type,format_type)函数

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

案例：使用GET_FORMAT()函数显示不同格式化下的格式字符串，SQL语句如下：

命令语句：select GET_FORMAT(DATE,’EUR’),GET_FORMAT(DATE,’USA’);

select DATE_FORMAT(‘2018-8-12 20:50:10’,GET_FORMAT(DATE,’USA’));

执行结果：

未完...待续...

SQL语法总结：

使用函数：【select 函数名(参数1,参数2…);】