MYSQL常用日期和时间函数

﹏ヽ暗。殇╰゛Y 2022-06-09 04:12 320阅读 0赞

**“今夜偏知春气暖,虫声新透绿窗纱”**  
有时我们可能会遇到这样的需求：当前时间是多少、下个月的今天是星期几、统计截止到当前日期前 3 天的收入总和等。这些需求就需要日期和时间函数来实现。  
**CURDATE()函数：**返回当前日期，只包含年月日。

mysql> select CURDATE(); +------------+
    | CURDATE() | +------------+
    | 2007-07-11 | +------------+
    1 row in set (0.03 sec)

**CURTIME()函数：**返回当前时间，只包含时分秒。

mysql> select CURTIME(); +-----------+
    | CURTIME() | +-----------+
    | 14:13:46 | +-----------+
    1 row in set (0.00 sec)

**NOW()函数：**返回当前的日期和时间，年月日时分秒全都包含。

mysql> select NOW(); +---------------------+
    | NOW() | +---------------------+
    | 2007-07-11 14:14:06 | +---------------------+
    1 row in set (0.00 sec)

**UNIX\_TIMESTAMP(date)函数：**返回日期 date 的 UNIX 时间戳。

mysql> select UNIX_TIMESTAMP(now()); +-----------------------+
    | UNIX_TIMESTAMP(now()) | +-----------------------+
    | 1184134516 | +-----------------------+
    1 row in set (0.02 sec)

**FROM\_UNIXTIME （ unixtime ） 函 数 ：** 返 回 UNIXTIME 时 间 戳 的 日 期 值 ， 和UNIX\_TIMESTAMP(date)互为逆操作。

mysql> select FROM_UNIXTIME(1184134516) ; +---------------------------+
    | FROM_UNIXTIME(1184134516) | +---------------------------+
    | 2007-07-11 14:15:16 | +---------------------------+
    1 row in set (0.00 sec)

**WEEK(DATE)和 YEAR(DATE)函数：**前者返回所给的日期是一年中的第几周，后者返回所给的日期是哪一年。

mysql> select WEEK(now()),YEAR(now()); +-------------+-------------+
    | WEEK(now()) | YEAR(now()) | +-------------+-------------+
    | 27 | 2007 | +-------------+-------------+
    1 row in set (0.02 sec)

**HOUR(time)和 MINUTE(time)函数：**前者返回所给时间的小时，后者返回所给时间的分钟。

mysql> select HOUR(CURTIME()),MINUTE(CURTIME()); +-----------------+-------------------+
    | HOUR(CURTIME()) | MINUTE(CURTIME()) | +-----------------+-------------------+
    | 14 | 18 | +-----------------+-------------------+
    1 row in set (0.00 sec)

**MONTHNAME(date)函数：**返回 date 的英文月份名称。

mysql> select MONTHNAME(now()); +------------------+
    | MONTHNAME(now()) | +------------------+
    | July | +------------------+
    1 row in set (0.00 sec)

DATE\_FORMAT(date,fmt)函数：按字符串 fmt 格式化日期 date 值，此函数能够按指定的格式显示日期，

格式符  格式说明
    %S,%s   两位数字形式的秒（00,01,...,59）
    %i      两位数字形式的分（00,01,...,59）
    %H      两位数字形式的小时，24 小时（00,01,...,23）
    %h,%I   两位数字形式的小时，12 小时（01,02,...,12）
    %k      数字形式的小时，24 小时（0,1,...,23）
    %l      数字形式的小时，12 小时（1,2,...,12）
    %T      24 小时的时间形式（hh:mm:ss）
    %r      12 小时的时间形式（hh:mm:ssAM 或 hh:mm:ssPM）
    %p      AM 或 PM
    %W      一周中每一天的名称（Sunday,Monday,...,Saturday）
    %a      一周中每一天名称的缩写（Sun,Mon,...,Sat）
    %d      两位数字表示月中的天数（00,01,...,31）
    %e      数字形式表示月中的天数（1,2，...,31）
    %D      英文后缀表示月中的天数（1st,2nd,3rd,...）
    %w      以数字形式表示周中的天数                     （0=Sunday,1=Monday,...,6=Saturday）
    %j      以 3 位数字表示年中的天数（001,002,...,366）
    %U      周（0,1,52），其中 Sunday 为周中的第一天
    %u      周（0,1,52），其中 Monday 为周中的第一天
    %M      月名（January,February,...,December）
    %b      缩写的月名（January,February,...,December）
    %m      两位数字表示的月份（01,02,...,12）
    %c      数字表示的月份（1,2,...,12）
    %Y      4 位数字表示的年份
    %y      两位数字表示的年份
    %%      直接值“%”

下面的例子将当前时间显示为“月，日，年”格式：

mysql> select DATE_FORMAT(now(),'%M,%D,%Y'); +-------------------------------+
    | DATE_FORMAT(now(),'%M,%D,%Y') | +-------------------------------+
    | July,11th,2007 | +-------------------------------+
    1 row in set (0.00 sec)

**DATE\_ADD(date,INTERVAL expr type)函数：**返回与所给日期 date 相差 INTERVAL 时间段的日期。其中 INTERVAL 是间隔类型关键字，expr 是一个表达式，这个表达式对应后面的类型，type是间隔类型，MySQL 提供了 13 种间隔类型

表达式类型   描述      格式
    HOUR        小时      hh
    MINUTE      分       mm
    SECOND      秒   ss
    YEAR        年   YY
    MONTH       月   MM
    DAY         日   DD
    YEAR_MONTH  年和月  YY-MM
    DAY_HOUR    日和小时  DD hh
    DAY_MINUTE  日和分钟  DD hh:mm
    DAY_ SECOND  日和秒  DD hh:mm:ss
    HOUR_MINUTE  小时和分  hh:mm
    HOUR_SECOND  小时和秒  hh:ss
    MINUTE_SECOND  分钟和秒  mm:ss

来看一个具体的例子，在这个例子中第 1 列返回了当前日期时间，第 2 列返回距离当前日期31 天后的日期时间，第 3 列返回距离当前日期一年两个月后的日期时间。

mysql> select now() current,date_add(now(),INTERVAL 31 day) after31days,
    date_add(now(),INTERVAL '1_2' year_month) after_oneyear_twomonth;
    +---------------------+---------------------+------------------------+
    | current | after31days | after_oneyear_twomonth |
    +---------------------+---------------------+------------------------+
    | 2007-09-03 11:30:48 | 2007-10-04 11:30:48 | 2008-11-03 11:30:48 |
    +---------------------+---------------------+------------------------+
    1 row in set (0.01 sec)

同样也可以用负数让它返回之前的某个日期时间，如下第 1 列返回了当前日期时间，第 2列返回距离当前日期 31 天前的日期时间，第 3 列返回距离当前日期一年两个月前的日期时间。

mysql> select now() current,date_add(now(),INTERVAL -31 day) after31days,date_a
    dd(now(),INTERVAL '-1_-2' year_month) after_oneyear_twomonth; +---------------------+---------------------+------------------------+
    | current | after31days | after_oneyear_twomonth | +---------------------+---------------------+------------------------+
    | 2007-09-03 11:36:35 | 2007-08-03 11:36:35 | 2006-07-03 11:36:35 | +---------------------+---------------------+------------------------+
    1 row in set (0.00 sec)

**DATEDIFF（date1，date2）函数：**用来计算两个日期之间相差的天数。  
下面的例子计算出当前距离 2008 年 8 月 8 日的奥运会开幕式还有多少天：

mysql> select DATEDIFF('2008-08-08',now()); +------------------------------+
    | DATEDIFF('2008-08-08',now()) | +------------------------------+
    | 328 | +------------------------------+
    1 row in set (0.01 sec)