数据库基本操作杂记(四)

深碍√TFBOYSˉ_ 2022-05-15 18:20 232阅读 0赞

1、当前日期时间函数，即返回DATETIME类型的数据

NOW();
    CURRENT_TIMESTAMP();
    LOCALTIME();
    LOCALTIMESTAMP();
    SYSDATE();

MariaDB [spring_batch_example]> SELECT NOW();
    +---------------------+
    | NOW()               |
    +---------------------+
    | 2018-10-30 23:42:40 |
    +---------------------+
    1 row in set (0.00 sec)

2、当前日期函数，即返回DATE类型的数据

CURDATE();
    CURRENT_DATE();
    CURRENT_DATE;

MariaDB [spring_batch_example]> SELECT CURDATE();
    +------------+
    | CURDATE()  |
    +------------+
    | 2018-10-30 |
    +------------+
    1 row in set (0.00 sec)

3、当前时间函数，即返回TIME类型的数据

CURTIME();
    CURRNET_TIME();
    CURRENT_TIME;

MariaDB [spring_batch_example]> SELECT CURTIME();
    +-----------+
    | CURTIME() |
    +-----------+
    | 23:44:48  |
    +-----------+
    1 row in set (0.00 sec)

4、提取

4.1从DATATIME中提取DATE或TIME类型

通过DATE()函数获取日期，通过TIME()获取时间

MariaDB [spring_batch_example]> SELECT create_time as '原始数据', DATE(create_time) as '日期', TIME(create_time) AS '时间' FROM batch_job_execution;
    +---------------------+------------+----------+
    | 原始数据             | 日期       | 时间     |
    +---------------------+------------+----------+
    | 2017-06-03 17:57:06 | 2017-06-03 | 17:57:06 |
    +---------------------+------------+----------+
    1 row in set (0.00 sec)

4.2 获取时分秒

通过HOUR()、MINUTE()、SECOND()分别获取时分秒数据，这三个函数可接受DATETIME、TIME、TIMESTAMP类型的数据

MariaDB [spring_batch_example]> SELECT HOUR(CURTIME()) AS '时', MINUTE(CURTIME()) AS '分', SECOND(CURTIME()) AS '秒';
    +------+------+------+
    | 时   | 分   | 秒   |
    +------+------+------+
    |   23 |   58 |   10 |
    +------+------+------+
    1 row in set (0.00 sec)

4.3 获取年月日

通过YEAR()、MONTH()、DAY()获取年月日，接受DATATIME、DATE类型或对应的字符串格式（例如'2018-10-31'）, 也可接受YYYYMMDD格式的数字（例如 20181031）

5、格式化日期和时间

使用DATE\_FORMAT()，TIME\_FORMAT()

MariaDB [spring_batch_example]> SELECT create_time, DATE_FORMAT(create_time, '%W, %M %e, %Y') AS 'date format', TIME_FORMAT(create_time, '%l:%i %p') AS 'time format' FROM batch_job_execution;
    +---------------------+------------------------+-------------+
    | create_time         | date format            | time format |
    +---------------------+------------------------+-------------+
    | 2017-06-03 17:57:06 | Saturday, June 3, 2017 | 5:57 PM     |
    +---------------------+------------------------+-------------+
    1 row in set (0.00 sec)

6、日期时间加减

MariaDB [spring_batch_example]> SELECT create_time, DATE_ADD(create_time, INTERVAL 1 YEAR), DATE_SUB(create_time, INTERVAL 1 YEAR) FROM batch_job_execution;
    +---------------------+----------------------------------------+----------------------------------------+
    | create_time         | DATE_ADD(create_time, INTERVAL 1 YEAR) | DATE_SUB(create_time, INTERVAL 1 YEAR) |
    +---------------------+----------------------------------------+----------------------------------------+
    | 2017-06-03 17:57:06 | 2018-06-03 17:57:06                    | 2016-06-03 17:57:06                    |
    +---------------------+----------------------------------------+----------------------------------------+
    1 row in set (0.00 sec)