MySQL 中的日期时间

ゝ一纸荒年。 2022-07-13 01:39 155阅读 0赞

## 关于日期时间的比较 ##

标准的 SQL 中，严格的 DATE 格式为 ‘YYYY-MM-DD’ ，如 ‘2018-05-06’ 。

为了方便，如果日期时间是在数值环境下使用的，MySQL 会自动将日期时间转换为数值（反之亦然）。

MySQL 相当智能，在与 TIMESTAMP、DATE 或 DATETIME 列进行比较的 WHERE 子句中，允许【宽松形式】的日期时间字符串。

宽松形式是指，任何标点字符都能用作各部分之间的分隔符。例如，’2016-08-15’ 和 ‘2016\#08\#15’ 是等同的。MySQL 还能转换不含任何分隔符的字符串（如 ‘20160815’），前提是它必须是有意义的日期时间。

使用 <、<=、=、>=、>、或 BETWEEN 等操作符，将 DATE、TIME、DATETIME 或 TIMESTAMP 与常量字符串进行比较时，MySQL 通常会自动将字符串转换为内部长整数，以便进行快速比较。

SELECT * FROM tbl_name WHERE date >= '2016-05-05';

但是，对于 IN 和 STRCMP，会执行字符串比较。

## 常用的日期时间函数 ##

### now ###

返回当前的日期和时间。

MariaDB [test]> select now();
    +---------------------+
    | now() | +---------------------+
    | 2016-11-20 21:46:56 | +---------------------+

### curdate ###

返回当前的日期。curdate()是current\_date()函数的缩写形式。

MariaDB [test]> select curdate();
    +------------+
    | curdate() | +------------+
    | 2016-11-20 | +------------+
    1 row in set (0.00 sec)
    
    MariaDB [test]> select current_date(); +----------------+ | current_date() |
    +----------------+
    | 2016-11-20 | +----------------+

### curtime ###

返回当前的时间，curtime()是current\_time()函数的缩写形式。

MariaDB [test]> select curtime();
    +-----------+
    | curtime() | +-----------+
    | 21:51:55 | +-----------+

### unix\_timestamp ###

返回指定时间的时间戳，如果参数为空，默认是当前时间。

MariaDB [test]> select unix_timestamp(); +------------------+ | unix_timestamp() |
    +------------------+
    | 1479650714 | +------------------+
    1 row in set (0.00 sec)
    
    MariaDB [test]> select unix_timestamp('2016-11-20'); +------------------------------+ | unix_timestamp('2016-11-20') |
    +------------------------------+
    | 1479571200 | +------------------------------+
    1 row in set (0.00 sec)
    
    MariaDB [test]> select unix_timestamp('2016-11-20 21:56:30'); +---------------------------------------+ | unix_timestamp('2016-11-20 21:56:30') |
    +---------------------------------------+
    | 1479650190 | +---------------------------------------+

### from\_unixtime ###

返回指定时间戳的时间格式。

MariaDB [test]> select from_unixtime(1479650190); +---------------------------+ | from_unixtime(1479650190) |
    +---------------------------+
    | 2016-11-20 21:56:30 | +---------------------------+

### year ###

返回指定时间的年份。

MariaDB [test]> select year('2016-11-20');
    +--------------------+
    | year('2016-11-20') | +--------------------+
    | 2016 | +--------------------+
    1 row in set (0.00 sec)
    
    MariaDB [test]> select year(now());
    +-------------+
    | year(now()) | +-------------+
    | 2016 | +-------------+

### datediff ###

返回两个日期的相差天数。

MariaDB [test]> select datediff(now(), '2016-11-18');
    +-------------------------------+
    | datediff(now(), '2016-11-18') | +-------------------------------+
    | 2 | +-------------------------------+
    1 row in set (0.00 sec)
    
    MariaDB [test]> select datediff(now(), '2016-11-18 21:00:00');
    +----------------------------------------+
    | datediff(now(), '2016-11-18 21:00:00') | +----------------------------------------+
    | 2 | +----------------------------------------+
    1 row in set (0.00 sec)
    
    MariaDB [test]> select datediff(curdate(), '2016-11-25');
    +-----------------------------------+
    | datediff(curdate(), '2016-11-25') | +-----------------------------------+
    | -5 | +-----------------------------------+

### to\_days ###

将日期格式的时间转换为以天为单位的时间，以便进行天数的比较。

MariaDB [test]> select to_days('0000-01-01'); +-----------------------+ | to_days('0000-01-01') |
    +-----------------------+
    | 1 | +-----------------------+
    1 row in set (0.00 sec)
    
    MariaDB [test]> select to_days(curdate()); +--------------------+ | to_days(curdate()) |
    +--------------------+
    | 736653 | +--------------------+
    1 row in set (0.00 sec)
    
    MariaDB [test]> select to_days(curdate()) - to_days('0000-01-01');
    +--------------------------------------------+
    | to_days(curdate()) - to_days('0000-01-01') | +--------------------------------------------+
    | 736652 | +--------------------------------------------+
    1 row in set (0.00 sec)
    
    MariaDB [test]> select to_days(curdate()) - to_days('2016-11-18');
    +--------------------------------------------+
    | to_days(curdate()) - to_days('2016-11-18') | +--------------------------------------------+
    | 2 | +--------------------------------------------+
    1 row in set (0.00 sec)
    
    MariaDB [test]> select to_days(curdate()) - to_days('2016-11-25');
    +--------------------------------------------+
    | to_days(curdate()) - to_days('2016-11-25') | +--------------------------------------------+
    | -5 | +--------------------------------------------+

### date\_format ###

按照指定的格式，对日期时间字符串进行格式化。

常用的格式说明符有以下几种：

*  %Y： 年份（4位数字）
 *  %m： 月份（2位数字）
 *  %d： 该月的哪一天（2位数字）
 *  %H： 24小时制的小时（2位数字）
 *  %i： 分钟（2位数字）
 *  %s： 秒数（2位数字）

MariaDB [test]> select date_format(now(), '%Y-%m'); +-----------------------------+ | date_format(now(), '%Y-%m') |
    +-----------------------------+
    | 2017-12 | +-----------------------------+

### time\_format ###

按照指定的格式，对时间（时分秒）字符串进行格式化。

mysql> select time_format('09:30', '%H:%i:00'); +----------------------------------+
    | time_format('09:30', '%H:%i:00') | +----------------------------------+
    | 09:30:00 | +----------------------------------+

### time\_to\_sec ###

将时间字符串转化为秒。

mysql> select time_to_sec('00:30'); +----------------------+
    | time_to_sec('00:30') | +----------------------+
    | 1800 | +----------------------+

### date\_add ###

给日期时间字符串一个增量，增量的类型（type）可以是以下类型：

*  YEAR
 *  MONTH
 *  DAY
 *  WEEK
 *  HOUR
 *  MINUTE
 *  SECOND
 *  MICROSECOND
 *  QUARTER
 *  YEAR\_MONTH
 *  DAY\_HOUR
 *  DAY\_MINUTE
 *  DAY\_SECOND
 *  DAY\_MICROSECOND
 *  HOUR\_MINUTE
 *  HOUR\_SECOND
 *  HOUR\_MICROSECOND
 *  MINUTE\_SECOND
 *  MINUTE\_MICROSECOND
 *  SECOND\_MICROSECOND

说明：MySQL 中的关键字不区分大小写，但对于关键字，还是推荐使用大写。

mysql> select DATE_ADD('2016-05-05', INTERVAL 2 DAY); +----------------------------------------+
    | DATE_ADD('2016-05-05', INTERVAL 2 DAY) | +----------------------------------------+
    | 2016-05-07 | +----------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select DATE_ADD('2016-05-05 08:30:30', INTERVAL 2 DAY); +-------------------------------------------------+
    | DATE_ADD('2016-05-05 08:30:30', INTERVAL 2 DAY) | +-------------------------------------------------+
    | 2016-05-07 08:30:30 | +-------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select DATE_ADD('2016-05-05 08:30:30', INTERVAL 8 HOUR); +--------------------------------------------------+
    | DATE_ADD('2016-05-05 08:30:30', INTERVAL 8 HOUR) | +--------------------------------------------------+
    | 2016-05-05 16:30:30 | +--------------------------------------------------+
    1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('2016-07-15 08:30:30', INTERVAL '2 4' YEAR_MONTH) AS res; +---------------------+
    | res | +---------------------+
    | 2018-11-15 08:30:30 | +---------------------+
    
    mysql> SELECT DATE_ADD('2016-07-15 08:30:30', INTERVAL '02:10:15' HOUR_SECOND) AS res; +---------------------+
    | res | +---------------------+
    | 2016-07-15 10:40:45 | +---------------------+

### date\_sub ###

给日期时间字符串一个减量。

mysql> SELECT DATE_SUB('2016-07-15 08:30:30', INTERVAL 2 WEEK); +--------------------------------------------------+
    | DATE_SUB('2016-07-15 08:30:30', INTERVAL 2 WEEK) | +--------------------------------------------------+
    | 2016-07-01 08:30:30 | +--------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT DATE_SUB('2016-07-15 08:30:30', INTERVAL '1 1:1:1' DAY_SECOND); +----------------------------------------------------------------+
    | DATE_SUB('2016-07-15 08:30:30', INTERVAL '1 1:1:1' DAY_SECOND) | +----------------------------------------------------------------+
    | 2016-07-14 07:29:29 | +----------------------------------------------------------------+

### date ###

仅获取目标字符串中的日期（年月日）。

mysql> SELECT DATE(DATE_SUB('2016-07-15 08:30:30', INTERVAL 2 WEEK)); +--------------------------------------------------------+
    | DATE(DATE_SUB('2016-07-15 08:30:30', INTERVAL 2 WEEK)) | +--------------------------------------------------------+
    | 2016-07-01 | +--------------------------------------------------------+

### time ###

仅获取目标字符串中的时间（时分秒）。

mysql> SELECT TIME('2016-07-15 08:30:30'); +-----------------------------+
    | TIME('2016-07-15 08:30:30') | +-----------------------------+
    | 08:30:30 | +-----------------------------+
    1 row in set (0.01 sec)
    
    mysql> SELECT TIME('2016-07-15 08:30:30.251'); +---------------------------------+
    | TIME('2016-07-15 08:30:30.251') | +---------------------------------+
    | 08:30:30.251000 | +---------------------------------+
    1 row in set (0.00 sec)

mysql> SELECT TIME_FORMAT(TIME('2016-07-15 08:30:30.251'), '%H:%i:%s'); +----------------------------------------------------------+
    | TIME_FORMAT(TIME('2016-07-15 08:30:30.251'), '%H:%i:%s') | +----------------------------------------------------------+
    | 08:30:30 | +----------------------------------------------------------+

### timestampdiff ###

计算两个日期时间的时间差。

mysql> SELECT TIMESTAMPDIFF(YEAR,'2015-03-20','2015-05-01'); +-----------------------------------------------+
    | TIMESTAMPDIFF(YEAR,'2015-03-20','2015-05-01') | +-----------------------------------------------+
    | 0 | +-----------------------------------------------+
    
    mysql> SELECT TIMESTAMPDIFF(MONTH,'2015-03-20','2015-05-01'); +------------------------------------------------+
    | TIMESTAMPDIFF(MONTH,'2015-03-20','2015-05-01') | +------------------------------------------------+
    | 1 | +------------------------------------------------+
    
    mysql> SELECT TIMESTAMPDIFF(MINUTE,'2015-03-20 08:00:00','2015-03-21 08:01:00'); +-------------------------------------------------------------------+
    | TIMESTAMPDIFF(MINUTE,'2015-03-20 08:00:00','2015-03-21 08:01:00') | +-------------------------------------------------------------------+
    | 1441 | +-------------------------------------------------------------------+