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. 查询时间间隔

  • 一小时前
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
mysql> SELECT NOW() - INTERVAL 1 HOUR;
+-------------------------+
| NOW() - INTERVAL 1 HOUR |
+-------------------------+
| 2019-11-16 10:08:57     |
+-------------------------+
1
2
3
4
5
6
  • 今天
mysql> select CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2019-10-30 |
+------------+
1
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
  • 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

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

4. Hive SQL

  • 查询当前时间
SELECT from_unixtime(unix_timestamp()) // 2020-01-16 10:46:51
1

reference