mysql函数汇总之日期和时间函数

﹏ヽ暗。殇╰゛Y 2023-10-01 22:54 20阅读 0赞

日期和时间函数主要用来处理日期和时间值，一般的日期函数除了使用 `date` 类型的参数外，也可以使用 `datetime` 或者 `timestamp` 类型的参数，但会忽略这些值的时间部分。

![b9078de417ea5df279fae7bc00b850ff.jpeg][]

### 获取当前日期的函数 ###

`curdate()` 和 `current_date()` 函数的作用相同，将当前日期按照 `yyyy-mm-dd` 或 `yyyymmdd` 格式的值返回。

mysql> select curdate(), current_date(), curdate() + 0;
    +------------+----------------+---------------+
    | curdate()  | current_date() | curdate() + 0 |
    +------------+----------------+---------------+
    | 2022-07-12 | 2022-07-12     |      20220712 |
    +------------+----------------+---------------+
    1 row in set (0.00 sec)
    
    mysql>

从上面看到，两个函数的作用是相同的，都返回了相同的系统当前日期。

`curdate()+0` 将当前日期转换为数值型。

### 获取当前时间的函数 ###

`curtime()` 和 `current_time()` 函数的作用相同，将当前时间以 `hh:mm:ss` 或者 `hhmmss` 的格式返回。

mysql> select curtime(), current_time(), curtime()+0;
    +-----------+----------------+-------------+
    | curtime() | current_time() | curtime()+0 |
    +-----------+----------------+-------------+
    | 21:32:25  | 21:32:25       |      213225 |
    +-----------+----------------+-------------+
    1 row in set (0.00 sec)
    
    mysql>

从上面看到，两个函数的作用是相同的，都反悔了相同的系统当前时间。

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

`current_timestamp()` 、 `localtime()` 、 `now()` 和 `sysdate()` 这4个函数的作用相同，均为返回当前日期和时间的值。

mysql> select current_timestamp(), localtime(), now(), sysdate();
    +---------------------+---------------------+---------------------+---------------------+
    | current_timestamp() | localtime()         | now()               | sysdate()           |
    +---------------------+---------------------+---------------------+---------------------+
    | 2022-07-12 21:34:52 | 2022-07-12 21:34:52 | 2022-07-12 21:34:52 | 2022-07-12 21:34:52 |
    +---------------------+---------------------+---------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql>

从上面看到，4个函数返回的结果是一样的。

### UNIX时间戳函数 ###

`UNIX_TIMESTAMP(date)` 若无参数调用，则返回一个 `UNIX` 时间戳（‘1970-01-01 00:00:00’GMT之后的秒数）作为无符号整数。

其中， `GMT（Green wich mean time）` 为格林尼治标准时间。若用 `date` 来调用 `UNIX_TIMESTAMP()` ，它会将参数值以 `1970-01-0100:00:00GMT` 后的秒数的形式返回。

`date` 可以是一个 `DATE` 字符串、 `DATETIME` 字符串、 `TIMESTAMP` 或一个当地时间的 `YYMMDD` 或 `YYYYMMDD` 格式的数字。

mysql> select unix_timestamp(), unix_timestamp(now()), now();
    +------------------+-----------------------+---------------------+
    | unix_timestamp() | unix_timestamp(now()) | now()               |
    +------------------+-----------------------+---------------------+
    |       1657633074 |            1657633074 | 2022-07-12 21:37:54 |
    +------------------+-----------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql>

`from_unixtime(date)` 函数把 `unix` 时间戳转换为普通格式时间，与 `unix_timestamp(date)` 函数互为反函数。示例：

mysql> select from_unixtime('1657633074');
    +-----------------------------+
    | from_unixtime('1657633074') |
    +-----------------------------+
    | 2022-07-12 21:37:54.000000  |
    +-----------------------------+
    1 row in set (0.00 sec)
    
    mysql>

### 返回UTC日期的函数 ###

`utc_date()` 函数返回当前 `utc` (世界标准时间)日期值，其格式为 `yyyy-mm-dd` 或者 `yyyymmdd` ；

mysql> select utc_date(), utc_date()+0;
    +------------+--------------+
    | utc_date() | utc_date()+0 |
    +------------+--------------+
    | 2022-07-12 |     20220712 |
    +------------+--------------+
    1 row in set (0.00 sec)
    
    mysql>

### 返回UTC时间的函数 ###

`utc_time()` 返回当前 `utc` 时间值，格式为 `hh:mm:ss` 或者 `hhmmss` ；

mysql> select utc_time(), utc_time()+0;
    +------------+--------------+
    | utc_time() | utc_time()+0 |
    +------------+--------------+
    | 13:43:24   |       134324 |
    +------------+--------------+
    1 row in set (0.00 sec)
    
    mysql>

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

`month(date)` 函数返回 `date` 对应的月份。

mysql> select month('2022-12-12') as coll, month('20221212') as coll_1, month('221212') as coll_2;
    +------+--------+--------+
    | coll | coll_1 | coll_2 |
    +------+--------+--------+
    |   12 |     12 |     12 |
    +------+--------+--------+
    1 row in set (0.00 sec)
    
    mysql>

`monthname(date)` 函数返回日期 `date` 对应月份的英文全名；

mysql> select monthname('2022-12-12'), monthname('20221212'), monthname('221212');
    +-------------------------+-----------------------+---------------------+
    | monthname('2022-12-12') | monthname('20221212') | monthname('221212') |
    +-------------------------+-----------------------+---------------------+
    | December                | December              | December            |
    +-------------------------+-----------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql>

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

`dayname(d)` 函数返回 `d` 对应的工作日的英文名称；

mysql> select dayname('2022-07-12');
    +-----------------------+
    | dayname('2022-07-12') |
    +-----------------------+
    | Tuesday               |
    +-----------------------+
    1 row in set (0.00 sec)
    
    mysql>

`dayofweek(d)` 函数返回 `d` 对应的一周中的索引（位置，1表示周日，2表示周一，以此类推，7表示周六）；

mysql> select dayofweek('2022-07-12') as coll, dayofweek('2022-07-13') as coll_1;
    +------+--------+
    | coll | coll_1 |
    +------+--------+
    |    3 |      4 |
    +------+--------+
    1 row in set (0.00 sec)
    
    mysql>

`weekday(d)` 返回 `d` 对应的工作日索引：0表示周一，1表示周二，以此类推，6表示周日。

mysql> select weekday('2022-07-12') as coll, weekday('2022-07-13') as coll_1;
    +------+--------+
    | coll | coll_1 |
    +------+--------+
    |    1 |      2 |
    +------+--------+
    1 row in set (0.00 sec)
    
    mysql>

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

`week(d)` 计算日期 `d` 是一年中的第几周。

`week()` 的双参数形式允许指定该星期是否始于周日或者周一，以及返回值的范围是否为0~53或者1~53.若 `mode` 参数被省略，则使用 `default_week_format` 系统自变量的值，如下图：

![f33d2938938f78f0c302691dc2149371.png][]

例如：使用 `week()` 函数查询指定日期是一年中的第几周；

mysql> select week('2022-07-13'), week('2022-01-01'), week('2022-09-18');
    +--------------------+--------------------+--------------------+
    | week('2022-07-13') | week('2022-01-01') | week('2022-09-18') |
    +--------------------+--------------------+--------------------+
    |                 28 |                  0 |                 38 |
    +--------------------+--------------------+--------------------+
    1 row in set (0.00 sec)
    
    mysql>

`weekofyear(d)` 计算某一天位于一年中的第几周，范围是1~53，相当于 `week(d,3)` ；

例如：使用 `weekofyear(d)` 查询指定日期是一年中的第几周；

mysql> select weekofyear('2022-07-13'), week('2022-07-13', 3);
    +--------------------------+-----------------------+
    | weekofyear('2022-07-13') | week('2022-07-13', 3) |
    +--------------------------+-----------------------+
    |                       28 |                    28 |
    +--------------------------+-----------------------+
    1 row in set (0.00 sec)
    
    mysql>

从上面可以看出，两个函数返回结果相同。

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

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

例如：使用 `dayofyear()` 函数返回指定日期在一年中的位置；

mysql> select dayofyear('2022-07-13'), dayofyear('2022-01-01');
    +-------------------------+-------------------------+
    | dayofyear('2022-07-13') | dayofyear('2022-01-01') |
    +-------------------------+-------------------------+
    |                     194 |                       1 |
    +-------------------------+-------------------------+
    1 row in set (0.00 sec)
    
    mysql>

`dayofmonth(d)` 函数返回 `d` 是一个月中的第几天，范围是1~31；

例如：使用 `dayofmonth()` 函数返回指定日期在一个月中的位置，必须有具体年份才可以。

mysql> select dayofmonth('2022-07-13'), dayofmonth('220713'), dayofmonth('0713');
    +--------------------------+----------------------+--------------------+
    | dayofmonth('2022-07-13') | dayofmonth('220713') | dayofmonth('0713') |
    +--------------------------+----------------------+--------------------+
    |                       13 |                   13 |               NULL |
    +--------------------------+----------------------+--------------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql>

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

`year(date)` 返回 `date` 对应的年份，范围是 `1970~2069` ；

mysql> select year('2022-07-13'), year('20330909');
    +--------------------+------------------+
    | year('2022-07-13') | year('20330909') |
    +--------------------+------------------+
    |               2022 |             2033 |
    +--------------------+------------------+
    1 row in set (0.00 sec)
    
    mysql>

小提示：

`00~69` 转换为 `2000~2069` ， `70~99` 转换为 `1970~1999` 。

`quarter(date)` 返回 `date` 对应的一年中的季度值，范围是1~4；

mysql> select quarter('2022-07-13'), quarter('20330101');
    +-----------------------+---------------------+
    | quarter('2022-07-13') | quarter('20330101') |
    +-----------------------+---------------------+
    |                     3 |                   1 |
    +-----------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql>

`minute(time)` 返回 `time` 对应的分钟数，范围是0~59。

mysql> select minute('2022-07-13 09:09:09'), minute('06:06:06');
    +-------------------------------+--------------------+
    | minute('2022-07-13 09:09:09') | minute('06:06:06') |
    +-------------------------------+--------------------+
    |                             9 |                  6 |
    +-------------------------------+--------------------+
    1 row in set (0.00 sec)
    
    mysql>

`second(time)` 返回 `time` 对应的秒数，范围是0~59。

mysql> select second('2022-07-13 09:09:09'), second('06:06:06');
    +-------------------------------+--------------------+
    | second('2022-07-13 09:09:09') | second('06:06:06') |
    +-------------------------------+--------------------+
    |                             9 |                  6 |
    +-------------------------------+--------------------+
    1 row in set (0.00 sec)
    
    mysql>

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

`extract(type from date)` 函数所使用的时间间隔类型说明符与 `date_add()` 或 `date_sub()` 相同，但它从日期中提取一部分，而不是执行日期运算。

mysql> select extract(year from '2022-07-13') as coll, extract(year_month from '2022-07-13') as coll_1, extract(day_minute from '2022-07-13 09:08:07') as coll_2;
    +------+--------+--------+
    | coll | coll_1 | coll_2 |
    +------+--------+--------+
    | 2022 | 202207 | 130908 |
    +------+--------+--------+
    1 row in set (0.00 sec)
    
    mysql>

`type` 为不同值时，返回的值不同：

year
    year_month
    day_minute

### 时间和秒钟转换的函数 ###

`time_to_sec(time)` 返回已转化为秒的 `time` 参数。

转换公式为：小时\*3600+分钟\*60+秒

mysql> select time_to_sec('09:09:09');
    +-------------------------+
    | time_to_sec('09:09:09') |
    +-------------------------+
    |                   32949 |
    +-------------------------+
    1 row in set (0.00 sec)
    
    mysql>

`sec_to_time(seconds)` 返回被转化为小时、分钟和秒数的 `seconds` 参数值，其格式为 `hh:mm:ss` 或者 `hhmmss` 。

mysql> select time_to_sec('09:09:09'), sec_to_time(32949);
    +-------------------------+--------------------+
    | time_to_sec('09:09:09') | sec_to_time(32949) |
    +-------------------------+--------------------+
    |                   32949 | 09:09:09           |
    +-------------------------+--------------------+
    1 row in set (0.00 sec)
    
    mysql>

从上面可以看到， `time_to_sec` 和 `sec_to_time` 互为反函数。

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

计算日期和时间的函数有：

*  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` 为关键词，指示了表达式被解释的方式。
 *  ![6b53b82474dd007182a8500cc286f820.png][]

若 `date` 参数是一个 `date` 值，计算只会包含 `year` 、 `month` 和 `day` 部分（没有时间部分），其结果是一个 `date` 值，否则，结果将是一个 `datetime` 值。

`date_add(date, interval expr type)` 和 `adddate(date, intervar expr type)` 两个函数的作用相同，执行日期的加运算。

mysql> select date_add('2022-07-13 09:09:09', interval 1 second) as coll, adddate('2022-07-13 09:09:09', interval 1 second) as coll_1, date_add('2022-07-13 09:09:09', interval '1:1' minute_second) as coll_2;
    +---------------------+---------------------+---------------------+
    | coll                | coll_1              | coll_2              |
    +---------------------+---------------------+---------------------+
    | 2022-07-13 09:09:10 | 2022-07-13 09:09:10 | 2022-07-13 09:10:10 |
    +---------------------+---------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql>

`date_sub(date, interval expr type)` 或者 `subdate(date, interval expr type)` 两个函数的作用相同，执行日期的减运算。

mysql> select date_sub('2022-07-13 09:09:09', interval 31 day) as coll, subdate('2022-07-13 09:09:09', interval 31 day) as coll_1, date_sub('2022-07-13 09:09:09', interval '0 0:1:1' day_second) as coll_2;
    +---------------------+---------------------+---------------------+
    | coll                | coll_1              | coll_2              |
    +---------------------+---------------------+---------------------+
    | 2022-06-12 09:09:09 | 2022-06-12 09:09:09 | 2022-07-13 09:08:08 |
    +---------------------+---------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql>

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

mysql> select adddate('2022-07-13 09:09:09', '1:1:1'), addtime('09:09:09', '1:1:20');
    +-----------------------------------------+-------------------------------+
    | adddate('2022-07-13 09:09:09', '1:1:1') | addtime('09:09:09', '1:1:20') |
    +-----------------------------------------+-------------------------------+
    | 2022-07-14 09:09:09                     | 10:10:29                      |
    +-----------------------------------------+-------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql>

`subtime(date,expr)` 函数将 `date` 减去 `expr` 值，并返回修改后的值。

其中， `date` 是一个日期或者日期时间表达式，而 `expr` 是一个时间表达式。

mysql> select subtime('2020-07-13 09:09:09', '1:1:1 1:1:1'), subtime('2022-07-13 09:09:09', '1:1:1');
    +-----------------------------------------------+-----------------------------------------+
    | subtime('2020-07-13 09:09:09', '1:1:1 1:1:1') | subtime('2022-07-13 09:09:09', '1:1:1') |
    +-----------------------------------------------+-----------------------------------------+
    | 2020-07-13 08:08:08                           | 2022-07-13 08:08:08                     |
    +-----------------------------------------------+-----------------------------------------+
    1 row in set, 2 warnings (0.00 sec)
    
    mysql>

从上面可以看到，只计算了时间，日期并没有计算进去。

使用 `datediff()` 函数计算两个日期之间的间隔天数；

mysql> select datediff('2022-07-13 09:09:09', '2022-01-01') as coll, datediff('2022-07-13 09:09:09', '2022-07-10 08:08:08');
    +------+--------------------------------------------------------+
    | coll | datediff('2022-07-13 09:09:09', '2022-07-10 08:08:08') |
    +------+--------------------------------------------------------+
    |  193 |                                                      3 |
    +------+--------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql>

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

`date_format(date,format)` 根据 `format` 指定的格式显示 `date` 值。

主要 `format` 格式如下图：

![bd0715596bb132c55311018f0532a7e4.png][]

示例：使用 `date_format()` 函数格式化输出日期和时间值；

mysql> select date_format('2022-07-13 09:08:07', '%W %M %Y') as coll, date_format('2022-07-13 09:08:07', '%D %y %a %d %m %b %j') as coll_1;
    +---------------------+---------------------------+
    | coll                | coll_1                    |
    +---------------------+---------------------------+
    | Wednesday July 2022 | 13th 22 Wed 13 07 Jul 194 |
    +---------------------+---------------------------+
    1 row in set (0.00 sec)
    
    mysql>

`time_format(time, format)` 根据表达式 `format` 的要求显示时间 `time` 。

表达式 `format` 指定了显示的格式。因为 `time_format(time, format)` 只处理时间，所以 `format` 只使用时间格式。

示例：使用 `time_format()` 函数格式化输入时间值。

mysql> select time_format('13:14:15', '%H %k %h %I %l');
    +-------------------------------------------+
    | time_format('13:14:15', '%H %k %h %I %l') |
    +-------------------------------------------+
    | 13 13 01 01 1                             |
    +-------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql>

`get_format(val_typr, format_type)` 返回日期时间字符串的显示格式：

*  `val_type` 表示日期数据类型，包括 `date` 、 `datetime` 和 `time；`
 *  `format_type` 表示格式化显示类型，包括 `eur` 、 `interval` 、 `iso` 、 `jis` 、 `usa` 。

`get_format` 根据两个值类型组合返回的字符串显示格式如下：

![7d678bf488e67bf8dbf78ab90d7bd1c9.png][]

示例：使用 `get_format()` 函数显示不同格式化类型下的格式字符串。

mysql> select get_format(date, 'eur'), get_format(datetime, 'jis');
    +-------------------------+-----------------------------+
    | get_format(date, 'eur') | get_format(datetime, 'jis') |
    +-------------------------+-----------------------------+
    | %d.%m.%Y                | %Y-%m-%d %H:%i:%s           |
    +-------------------------+-----------------------------+
    1 row in set (0.00 sec)
    
    mysql> 
    mysql> select date_format('2022-07-13 09:08:07', get_format(datetime, 'jis')) as coll, date_format('2022-07-13 09:08:07', get_format(date, 'usa')) as coll_1;
    +---------------------+------------+
    | coll                | coll_1     |
    +---------------------+------------+
    | 2022-07-13 09:08:07 | 07.13.2022 |
    +---------------------+------------+
    1 row in set (0.00 sec)
    
    mysql>

至此，本文结束。

![c68c16e454d64d9bb22f50a03b46e666.jpeg][]

[b9078de417ea5df279fae7bc00b850ff.jpeg]: https://img-blog.csdnimg.cn/img_convert/b9078de417ea5df279fae7bc00b850ff.jpeg
[f33d2938938f78f0c302691dc2149371.png]: https://img-blog.csdnimg.cn/img_convert/f33d2938938f78f0c302691dc2149371.png
[6b53b82474dd007182a8500cc286f820.png]: https://img-blog.csdnimg.cn/img_convert/6b53b82474dd007182a8500cc286f820.png
[bd0715596bb132c55311018f0532a7e4.png]: https://img-blog.csdnimg.cn/img_convert/bd0715596bb132c55311018f0532a7e4.png
[7d678bf488e67bf8dbf78ab90d7bd1c9.png]: https://img-blog.csdnimg.cn/img_convert/7d678bf488e67bf8dbf78ab90d7bd1c9.png
[c68c16e454d64d9bb22f50a03b46e666.jpeg]: https://img-blog.csdnimg.cn/c68c16e454d64d9bb22f50a03b46e666.jpeg