SQL 查询时间间隔
1. 查询当天时间、日期
- 当前时间+日期、日期、时间
mysql> SELECT NOW(),CURDATE(),CURTIME();
+---------------------+------------+-----------+
| NOW() | CURDATE() | CURTIME() |
+---------------------+------------+-----------+
| 2019-11-16 11:03:52 | 2019-11-16 | 11:03:52 |
+---------------------+------------+-----------+
1
2
3
4
5
6
2
3
4
5
6
2. 查询时间间隔
- 一小时前
mysql> select DATE_SUB(NOW(), INTERVAL 1 HOUR);
+----------------------------------+
| DATE_SUB(NOW(), INTERVAL 1 HOUR) |
+----------------------------------+
| 2019-11-16 10:06:56 |
+----------------------------------+
1
2
3
4
5
6
2
3
4
5
6
mysql> SELECT NOW() - INTERVAL 1 HOUR;
+-------------------------+
| NOW() - INTERVAL 1 HOUR |
+-------------------------+
| 2019-11-16 10:08:57 |
+-------------------------+
1
2
3
4
5
6
2
3
4
5
6
- 今天
mysql> select CURDATE();
+------------+
| CURDATE() |
+------------+
| 2019-10-30 |
+------------+
1
2
3
4
5
6
2
3
4
5
6
- 昨天 和 前天
mysql> select DATE_SUB(CURDATE(),INTERVAL 1 DAY);
+------------------------------------+
| DATE_SUB(CURDATE(),INTERVAL 1 DAY) |
+------------------------------------+
| 2019-10-29 |
+------------------------------------+
mysql> select DATE_SUB(CURDATE(),INTERVAL 2 DAY);
+------------------------------------+
| DATE_SUB(CURDATE(),INTERVAL 2 DAY) |
+------------------------------------+
| 2019-10-28 |
+------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
CURRENT_DATE()
和CURDATE()
相同
mysql> SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY);
+------------------------------------------+
| DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY) |
+------------------------------------------+
| 2019-10-28 |
+------------------------------------------+
1
2
3
4
5
6
2
3
4
5
6
3. 查询示例
mysql> select * from capacity where date=CURDATE();
+----+----------+------------+---------------------+
| id | capacity | date | time |
+----+----------+------------+---------------------+
| 5 | 224 | 2019-10-30 | 2019-10-30 09:34:48 |
+----+----------+------------+---------------------+
mysql> select * from capacity where date=DATE_SUB(CURDATE(), INTERVAL 1 DAY);
+----+----------+------------+---------------------+
| id | capacity | date | time |
+----+----------+------------+---------------------+
| 4 | 210 | 2019-10-29 | 2019-10-30 09:34:48 |
+----+----------+------------+---------------------+
mysql> select * from capacity where date=DATE_SUB(CURDATE(), INTERVAL 2 DAY);
+----+----------+------------+---------------------+
| id | capacity | date | time |
+----+----------+------------+---------------------+
| 3 | 200 | 2019-10-28 | 2019-10-30 09:34:48 |
+----+----------+------------+---------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
4. Hive SQL
- 查询当前时间
SELECT from_unixtime(unix_timestamp()) // 2020-01-16 10:46:51
1
reference
- [1] w3school. MySQL DATE_SUB() 函数open in new window
- [2] sfl. Sql query to select from 1 hour ago?open in new window