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

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

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

b9078de417ea5df279fae7bc00b850ff.jpeg

获取当前日期的函数

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

  1. mysql> select curdate(), current_date(), curdate() + 0;
  2. +------------+----------------+---------------+
  3. | curdate()  | current_date() | curdate() + 0 |
  4. +------------+----------------+---------------+
  5. | 2022-07-12 | 2022-07-12     |      20220712 |
  6. +------------+----------------+---------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

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

获取当前时间的函数

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

  1. mysql> select curtime(), current_time(), curtime()+0;
  2. +-----------+----------------+-------------+
  3. | curtime() | current_time() | curtime()+0 |
  4. +-----------+----------------+-------------+
  5. | 21:32:25  | 21:32:25       |      213225 |
  6. +-----------+----------------+-------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

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

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

  1. mysql> select current_timestamp(), localtime(), now(), sysdate();
  2. +---------------------+---------------------+---------------------+---------------------+
  3. | current_timestamp() | localtime()         | now()               | sysdate()           |
  4. +---------------------+---------------------+---------------------+---------------------+
  5. | 2022-07-12 21:34:52 | 2022-07-12 21:34:52 | 2022-07-12 21:34:52 | 2022-07-12 21:34:52 |
  6. +---------------------+---------------------+---------------------+---------------------+
  7. 1 row in set (0.00 sec)
  9. 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 格式的数字。

  1. mysql> select unix_timestamp(), unix_timestamp(now()), now();
  2. +------------------+-----------------------+---------------------+
  3. | unix_timestamp() | unix_timestamp(now()) | now()               |
  4. +------------------+-----------------------+---------------------+
  5. |       1657633074 |            1657633074 | 2022-07-12 21:37:54 |
  6. +------------------+-----------------------+---------------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

  1. mysql> select from_unixtime('1657633074');
  2. +-----------------------------+
  3. | from_unixtime('1657633074') |
  4. +-----------------------------+
  5. | 2022-07-12 21:37:54.000000  |
  6. +-----------------------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

返回UTC日期的函数

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

  1. mysql> select utc_date(), utc_date()+0;
  2. +------------+--------------+
  3. | utc_date() | utc_date()+0 |
  4. +------------+--------------+
  5. | 2022-07-12 |     20220712 |
  6. +------------+--------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

返回UTC时间的函数

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

  1. mysql> select utc_time(), utc_time()+0;
  2. +------------+--------------+
  3. | utc_time() | utc_time()+0 |
  4. +------------+--------------+
  5. | 13:43:24   |       134324 |
  6. +------------+--------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

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

  1. mysql> select month('2022-12-12') as coll, month('20221212') as coll_1, month('221212') as coll_2;
  2. +------+--------+--------+
  3. | coll | coll_1 | coll_2 |
  4. +------+--------+--------+
  5. |   12 |     12 |     12 |
  6. +------+--------+--------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

  1. mysql> select monthname('2022-12-12'), monthname('20221212'), monthname('221212');
  2. +-------------------------+-----------------------+---------------------+
  3. | monthname('2022-12-12') | monthname('20221212') | monthname('221212') |
  4. +-------------------------+-----------------------+---------------------+
  5. | December                | December              | December            |
  6. +-------------------------+-----------------------+---------------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

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

  1. mysql> select dayname('2022-07-12');
  2. +-----------------------+
  3. | dayname('2022-07-12') |
  4. +-----------------------+
  5. | Tuesday               |
  6. +-----------------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

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

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

  1. mysql> select weekday('2022-07-12') as coll, weekday('2022-07-13') as coll_1;
  2. +------+--------+
  3. | coll | coll_1 |
  4. +------+--------+
  5. |    1 |      2 |
  6. +------+--------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

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

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

f33d2938938f78f0c302691dc2149371.png

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

  1. mysql> select week('2022-07-13'), week('2022-01-01'), week('2022-09-18');
  2. +--------------------+--------------------+--------------------+
  3. | week('2022-07-13') | week('2022-01-01') | week('2022-09-18') |
  4. +--------------------+--------------------+--------------------+
  5. |                 28 |                  0 |                 38 |
  6. +--------------------+--------------------+--------------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

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

  1. mysql> select weekofyear('2022-07-13'), week('2022-07-13', 3);
  2. +--------------------------+-----------------------+
  3. | weekofyear('2022-07-13') | week('2022-07-13', 3) |
  4. +--------------------------+-----------------------+
  5. |                       28 |                    28 |
  6. +--------------------------+-----------------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

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

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

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

  1. mysql> select dayofyear('2022-07-13'), dayofyear('2022-01-01');
  2. +-------------------------+-------------------------+
  3. | dayofyear('2022-07-13') | dayofyear('2022-01-01') |
  4. +-------------------------+-------------------------+
  5. |                     194 |                       1 |
  6. +-------------------------+-------------------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

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

  1. mysql> select dayofmonth('2022-07-13'), dayofmonth('220713'), dayofmonth('0713');
  2. +--------------------------+----------------------+--------------------+
  3. | dayofmonth('2022-07-13') | dayofmonth('220713') | dayofmonth('0713') |
  4. +--------------------------+----------------------+--------------------+
  5. |                       13 |                   13 |               NULL |
  6. +--------------------------+----------------------+--------------------+
  7. 1 row in set, 1 warning (0.00 sec)
  9. mysql>

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

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

  1. mysql> select year('2022-07-13'), year('20330909');
  2. +--------------------+------------------+
  3. | year('2022-07-13') | year('20330909') |
  4. +--------------------+------------------+
  5. |               2022 |             2033 |
  6. +--------------------+------------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

小提示：

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

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

  1. mysql> select quarter('2022-07-13'), quarter('20330101');
  2. +-----------------------+---------------------+
  3. | quarter('2022-07-13') | quarter('20330101') |
  4. +-----------------------+---------------------+
  5. |                     3 |                   1 |
  6. +-----------------------+---------------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

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

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

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

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

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

  1. 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;
  2. +------+--------+--------+
  3. | coll | coll_1 | coll_2 |
  4. +------+--------+--------+
  5. | 2022 | 202207 | 130908 |
  6. +------+--------+--------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

  1. ​year​
  2. ​year_month​
  3. ​day_minute​

时间和秒钟转换的函数

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

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

  1. mysql> select time_to_sec('09:09:09');
  2. +-------------------------+
  3. | time_to_sec('09:09:09') |
  4. +-------------------------+
  5. |                   32949 |
  6. +-------------------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

  1. mysql> select time_to_sec('09:09:09'), sec_to_time(32949);
  2. +-------------------------+--------------------+
  3. | time_to_sec('09:09:09') | sec_to_time(32949) |
  4. +-------------------------+--------------------+
  5. |                   32949 | 09:09:09           |
  6. +-------------------------+--------------------+
  7. 1 row in set (0.00 sec)
  9. 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) 两个函数的作用相同，执行日期的加运算。

  1. 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;
  2. +---------------------+---------------------+---------------------+
  3. | coll                | coll_1              | coll_2              |
  4. +---------------------+---------------------+---------------------+
  5. | 2022-07-13 09:09:10 | 2022-07-13 09:09:10 | 2022-07-13 09:10:10 |
  6. +---------------------+---------------------+---------------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

  1. 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;
  2. +---------------------+---------------------+---------------------+
  3. | coll                | coll_1              | coll_2              |
  4. +---------------------+---------------------+---------------------+
  5. | 2022-06-12 09:09:09 | 2022-06-12 09:09:09 | 2022-07-13 09:08:08 |
  6. +---------------------+---------------------+---------------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

  1. mysql> select adddate('2022-07-13 09:09:09', '1:1:1'), addtime('09:09:09', '1:1:20');
  2. +-----------------------------------------+-------------------------------+
  3. | adddate('2022-07-13 09:09:09', '1:1:1') | addtime('09:09:09', '1:1:20') |
  4. +-----------------------------------------+-------------------------------+
  5. | 2022-07-14 09:09:09                     | 10:10:29                      |
  6. +-----------------------------------------+-------------------------------+
  7. 1 row in set, 1 warning (0.00 sec)
  9. mysql>

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

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

  1. 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');
  2. +-----------------------------------------------+-----------------------------------------+
  3. | subtime('2020-07-13 09:09:09', '1:1:1 1:1:1') | subtime('2022-07-13 09:09:09', '1:1:1') |
  4. +-----------------------------------------------+-----------------------------------------+
  5. | 2020-07-13 08:08:08                           | 2022-07-13 08:08:08                     |
  6. +-----------------------------------------------+-----------------------------------------+
  7. 1 row in set, 2 warnings (0.00 sec)
  9. mysql>

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

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

  1. 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');
  2. +------+--------------------------------------------------------+
  3. | coll | datediff('2022-07-13 09:09:09', '2022-07-10 08:08:08') |
  4. +------+--------------------------------------------------------+
  5. |  193 |                                                      3 |
  6. +------+--------------------------------------------------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

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

主要 format 格式如下图：

bd0715596bb132c55311018f0532a7e4.png

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

  1. 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;
  2. +---------------------+---------------------------+
  3. | coll                | coll_1                    |
  4. +---------------------+---------------------------+
  5. | Wednesday July 2022 | 13th 22 Wed 13 07 Jul 194 |
  6. +---------------------+---------------------------+
  7. 1 row in set (0.00 sec)
  9. mysql>

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

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

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

  1. mysql> select time_format('13:14:15', '%H %k %h %I %l');
  2. +-------------------------------------------+
  3. | time_format('13:14:15', '%H %k %h %I %l') |
  4. +-------------------------------------------+
  5. | 13 13 01 01 1                             |
  6. +-------------------------------------------+
  7. 1 row in set (0.00 sec)
  9. 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() 函数显示不同格式化类型下的格式字符串。

  1. mysql> select get_format(date, 'eur'), get_format(datetime, 'jis');
  2. +-------------------------+-----------------------------+
  3. | get_format(date, 'eur') | get_format(datetime, 'jis') |
  4. +-------------------------+-----------------------------+
  5. | %d.%m.%Y                | %Y-%m-%d %H:%i:%s           |
  6. +-------------------------+-----------------------------+
  7. 1 row in set (0.00 sec)
  9. mysql> 
  10. 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;
  11. +---------------------+------------+
  12. | coll                | coll_1     |
  13. +---------------------+------------+
  14. | 2022-07-13 09:08:07 | 07.13.2022 |
  15. +---------------------+------------+
  16. 1 row in set (0.00 sec)
  18. mysql>

至此，本文结束。

c68c16e454d64d9bb22f50a03b46e666.jpeg

发表评论

表情：
评论列表 （有 0 条评论，76人围观）

还没有评论，来说两句吧...

相关阅读

    相关 MYSQL日期和时间函数

    MYSQL日期和时间函数 大汇总这里是一个使用日期函数的例子。下面的查询选择了所有记录，其date\_col的值是在最后30天以内： [mysql][] SELECT some

    灰太狼灰太狼/ 0 赞/ 142 阅读