关于日期时间的比较
标准的 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 |
+-------------------------------------------------------------------+