作者:LAMP小白 点击:2023 发布日期:2012-10-18 00:38:00 返回列表
搜索那一天
mysql> SELECT * FROM president WHERE DAYOFMONTH(birth) = 1;
+-----------+------------+--------+--------+-------+------------+-------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+--------+-------+------------+-------+
| KAGAMI | MINAMI | NULL | TOYOTA | 15 | 2012-10-01 | NULL |
| mio | haha | a | CN | 16 | 2012-10-01 | NULL |
+-----------+------------+--------+--------+-------+------------+-------+
2 rows in set (0.00 sec)
计算两个日期的差
mysql> SELECT birth,death,TIMESTAMPDIFF(DAY, birth, death) AS days FROM president WHERE death IS NOT NULL;
+------------+------------+------+
| birth | death | days |
+------------+------------+------+
| 2012-09-03 | 2012-11-07 | 65 |
| 2012-09-02 | 2012-11-28 | 87 |
| 2012-09-10 | 2013-07-17 | 310 |
+------------+------------+------+
3 rows in set (0.00 sec)
日期间隔
小于某个日期的
mysql> SELECT birth,death FROM president WHERE birth <= '2012-09-20';
+------------+------------+
| birth | death |
+------------+------------+
| 2012-09-11 | NULL |
| 2012-09-03 | 2012-11-07 |
| 2012-09-02 | 2012-11-28 |
| 2012-09-10 | 2013-07-17 |
+------------+------------+
4 rows in set (0.00 sec)
查找据今天60天之内的
mysql> SELECT birth,death FROM president WHERE TO_DAYS(birth) - TO_DAYS(CURDATE()) < 60;
+------------+------------+
| birth | death |
+------------+------------+
| 2012-10-01 | NULL |
| 2012-10-01 | NULL |
| 2012-09-11 | NULL |
| 2012-09-03 | 2012-11-07 |
| 2012-09-02 | 2012-11-28 |
| 2012-09-10 | 2013-07-17 |
+------------+------------+
6 rows in set (0.00 sec)
日期值
之后:DATE_ADD 之前:DATE_SUB
mysql> SELECT DATE_ADD(CURDATE(), INTERVAL 10 YEAR);
+---------------------------------------+
| DATE_ADD(CURDATE(), INTERVAL 10 YEAR) |
+---------------------------------------+
| 2022-10-17 |
+---------------------------------------+
1 row in set (0.00 sec)
上一篇:NULL IF和表达式 下一篇:快递查询API