mysql中的时间函数以及格式问题

Yaurora
  2020-09-14 13:54:54 2020-09-14 13:54:54 创建   2023-03-20 18:15:04 2023-03-20 18:15:04 更新    931 字  5 分钟

之前在开发过程中， 碰到数据库关于时间的问题，总是要去搜索一下， 自己简单记录一下常用的吧，后面遇到啥新鲜的玩意， 再加上。

时间函数

  • 获取当前日期和时间

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    mysql> SELECT CURDATE(), CURRENT_DATE, CURTIME(), CURRENT_TIME;
    +------------+--------------+-----------+--------------+
    | CURDATE()  | CURRENT_DATE | CURTIME() | CURRENT_TIME |
    +------------+--------------+-----------+--------------+
    | 2020-09-14 | 2020-09-14   | 14:34:00  | 14:34:00     |
    +------------+--------------+-----------+--------------+

    mysql> SELECT NOW(), CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP;
    +---------------------+---------------------+---------------------+
    | NOW()               | CURRENT_TIMESTAMP() | CURRENT_TIMESTAMP   |
    +---------------------+---------------------+---------------------+
    | 2020-09-14 14:35:23 | 2020-09-14 14:35:23 | 2020-09-14 14:35:23 |
    +---------------------+---------------------+---------------------+

    -- 获取系统时间
    mysql> SELECT SYSDATE();
    +---------------------+
    | SYSDATE()           |
    +---------------------+
    | 2020-09-14 15:20:25 |
    +---------------------+

  • 获取当前时间的各个信息，如月份、一年第几周

    1
    2
    3
    4
    5
    6
    mysql> SELECT WEEK(NOW()), WEEK('2020-01-01'), DAY(CURRENT_TIMESTAMP()), DAY('2019-12-12 13:23:57'), YEAR(NOW()), MONTH(NOW());
    +-------------+--------------------+--------------------------+----------------------------+-------------+--------------+
    | WEEK(NOW()) | WEEK('2020-01-01') | DAY(CURRENT_TIMESTAMP()) | DAY('2019-12-12 13:23:57') | YEAR(NOW()) | MONTH(NOW()) |
    +-------------+--------------------+--------------------------+----------------------------+-------------+--------------+
    |          37 |                  0 |                       14 |                         12 |        2020 |            9 |
    +-------------+--------------------+--------------------------+----------------------------+-------------+--------------+

  • 关于时间戳转换

    1
    2
    3
    4
    5
    6
    mysql> SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP('2020-05-01 13:29:57'), FROM_UNIXTIME(1600066964), FROM_UNIXTIME(1600066964, '%m/%d/%Y');
    +------------------+---------------------------------------+---------------------------+---------------------------------------+
    | UNIX_TIMESTAMP() | UNIX_TIMESTAMP('2020-05-01 13:29:57') | FROM_UNIXTIME(1600066964) | FROM_UNIXTIME(1600066964, '%m/%d/%Y') |
    +------------------+---------------------------------------+---------------------------+---------------------------------------+
    |       1600067106 |                            1588310997 | 2020-09-14 15:02:44       | 09/14/2020                            |
    +------------------+---------------------------------------+---------------------------+---------------------------------------+

  • 自己提取时间中的信息

    1
    2
    3
    4
    5
    6
    7
    8
    -- 格式 SELECT EXTRACT(unit FROM date)
    -- eg.SELECT EXTRACT((SECOND|MINUTE|HOUR|DAY|WEEK|MONTH|YEAR|MICROSECOND|YEAR_DAY) FROM NOW())
    mysql> select extract(MONTH FROM NOW()), EXTRACT(YEAR FROM '2019-12-31'), EXTRACT(YEAR_MONTH FROM '2019-12-31');
    +---------------------------+---------------------------------+---------------------------------------+
    | extract(MONTH FROM NOW()) | EXTRACT(YEAR FROM '2019-12-31') | EXTRACT(YEAR_MONTH FROM '2019-12-31') |
    +---------------------------+---------------------------------+---------------------------------------+
    |                         9 |                            2019 |                                201912 |
    +---------------------------+---------------------------------+---------------------------------------+

  • 日期运算

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    -- DATE_ADD(date,INTERVAL expr type) 日期加上时间区间
    -- eg. DATE_ADD(now(), INTERVAL expr(YEAR|DAY|...|YEAR_MONTH|DAY_HOUR|DAY_MINUTE|DAY_SECOND|HOUR_SECOND))
    mysql>  SELECT DATE_ADD('2020-05-01 00:01:01', interval '1 1:20:29' day_second);
    +------------------------------------------------------------------+
    | DATE_ADD('2020-05-01 00:01:01', interval '1 1:20:29' day_second) |
    +------------------------------------------------------------------+
    | 2020-05-02 01:21:30                                              |
    +------------------------------------------------------------------+

    -- DATE_SUB(date,INTERVAL expr type) 日期减去时间区间
    mysql>  SELECT DATE_SUB('2020-05-02 01:21:30 ', interval '1 1:20:29' day_second);
    +-------------------------------------------------------------------+
    | DATE_SUB('2020-05-02 01:21:30 ', interval '1 1:20:29' day_second) |
    +-------------------------------------------------------------------+
    | 2020-05-01 00:01:01                                               |
    +-------------------------------------------------------------------+

    -- DATEDIFF(date1, date2) 日期相减
    mysql> SELECT DATEDIFF('2020-05-03', '2020-05-01'), DATEDIFF('2020-05-03 12:23:33', '2020-05-01 12:22:33');
    +--------------------------------------+--------------------------------------------------------+
    | DATEDIFF('2020-05-03', '2020-05-01') | DATEDIFF('2020-05-03 12:23:33', '2020-05-01 12:22:33') |
    +--------------------------------------+--------------------------------------------------------+
    |                                    2 |                                                      2 |
    +--------------------------------------+--------------------------------------------------------+

    -- TIMEDIFF(time1, time2)
    -- 注意两个日期类型的相减，默认是0
    mysql> SELECT  TIMEDIFF('12:23:33', '12:22:23'),TIMEDIFF('2020-05-03', '2020-05-01'), TIMEDIFF('2020-05-03 12:23:33', '2020-05-01 12:22:33');
    +----------------------------------+--------------------------------------+--------------------------------------------------------+
    | TIMEDIFF('12:23:33', '12:22:23') | TIMEDIFF('2020-05-03', '2020-05-01') | TIMEDIFF('2020-05-03 12:23:33', '2020-05-01 12:22:33') |
    +----------------------------------+--------------------------------------+--------------------------------------------------------+
    | 00:01:10                         | 00:00:00                             | 48:01:00                                               |
    +----------------------------------+--------------------------------------+--------------------------------------------------------+

时间格式转化

  • STR_TO_DATE()

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    -- 将字符串格式的时间转化为日期类型
    -- STR_TO_DATE(str, format)
    mysql> SELECT  STR_TO_DATE('08,23,20', '%m,%d,%y') , STR_TO_DATE('05/21/2020 12:23:33', '%m/%d/%Y %H:%i:%s'), STR_TO_DATE('21,5,2020 extra characters','%d,%m,%Y');
    +-------------------------------------+---------------------------------------------------------+------------------------------------------------------+
    | STR_TO_DATE('08,23,20', '%m,%d,%y') | STR_TO_DATE('05/21/2020 12:23:33', '%m/%d/%Y %H:%i:%s') | STR_TO_DATE('21,5,2020 extra characters','%d,%m,%Y') |
    +-------------------------------------+---------------------------------------------------------+------------------------------------------------------+
    | 2020-08-23                          | 2020-05-21 12:23:33                                     | 2020-05-21                                           |
    +-------------------------------------+---------------------------------------------------------+------------------------------------------------------+

    -- 看看格式化值为NULL的例子
    mysql> SELECT STR_TO_DATE('2020','%Y'), STR_TO_DATE('202005', "%Y%m") , STR_TO_DATE('11', '%h'), STR_TO_DATE('122223', '%H%i%s');
    +--------------------------+-------------------------------+-------------------------+---------------------------------+
    | STR_TO_DATE('2020','%Y') | STR_TO_DATE('202005', "%Y%m") | STR_TO_DATE('11', '%h') | STR_TO_DATE('122223', '%H%i%s') |
    +--------------------------+-------------------------------+-------------------------+---------------------------------+
    | NULL                     | NULL                          | NULL                    | NULL                            |
    +--------------------------+-------------------------------+-------------------------+---------------------------------+

  • DATE_FROMAT()

    1
    2
    3
    4
    5
    6
    7
    8
    -- 将标准格式转化为自己想要的格式
    -- DATE_FORMAT(date, format)
    mysql> SELECT DATE_FORMAT(now(), '%m/%d/%Y'), DATE_FORMAT('2019-05-01 12:22:33', '%m,%d,%Y, %H-%i-%s');
    +--------------------------------+----------------------------------------------------------+
    | DATE_FORMAT(now(), '%m/%d/%Y') | DATE_FORMAT('2019-05-01 12:22:33', '%m,%d,%Y, %H-%i-%s') |
    +--------------------------------+----------------------------------------------------------+
    | 09/14/2020                     | 05,01,2019, 12-22-33                                     |
    +--------------------------------+----------------------------------------------------------+
  • 标题: mysql中的时间函数以及格式问题
  • 作者: Yaurora
  • 创建于 : 2020-09-14 13:54:54
  • 更新于 : 2023-03-20 18:15:04
  • 链接: https://jingyu.life/2020/09/14/db/mysql/time-func/
  • 版权声明: 本文章采用 CC BY-NC-SA 4.0 进行许可。
此页目录
mysql中的时间函数以及格式问题
  1. 时间函数
    1. 获取当前日期和时间
    2. 获取当前时间的各个信息，如月份、一年第几周
    3. 关于时间戳转换
    4. 自己提取时间中的信息
    5. 日期运算
  2. 时间格式转化
    1. STR_TO_DATE()
    2. DATE_FROMAT()