BETWEEN 谓词

为方便使用，提供特殊的 BETWEEN 谓词。

语法

WHERE a BETWEEN x AND y

示例

WHERE a BETWEEN x AND y 

等效于：

WHERE a >= x AND a <= y

同样：

WHERE a NOT BETWEEN x AND y

等效于：

WHERE a < x OR a > y

可以为日期范围使用 BETWEEN 谓词：

=> CREATE TABLE t1 (c1 INT, c2 INT, c3 DATE);
=> COPY t1 FROM stdin DELIMITER '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1 | 2 | 2014-07-26
>> 2 | 3 | 2014-07-27
>> 3 | 4 | 2014-07-28
>> 4 | 5 | 2014-07-29
>> 5 | 6 | 2014-07-30
>> 6 | 7 | 2014-07-31
>> 7 | 8 | 2014-08-01
>> 8 | 9 | 2014-08-02
>> \.

=> SELECT* FROM t1 WHERE c3 BETWEEN DATE('2014-07-26') AND DATE('2014-07-30');
 c1 | c2 |     c3
----+----+------------
  1 |  2 | 2014-07-26
  2 |  3 | 2014-07-27
  3 |  4 | 2014-07-28
  4 |  5 | 2014-07-29
  5 |  6 | 2014-07-30
(5 rows)

也可以使用 NOW 和 INTERVAL 关键字从日期范围中选择：

=> SELECT * FROM t1 WHERE c3 BETWEEN NOW()-INTERVAL '1 week' AND NOW();
 c1 | c2 |     c3
----+----+------------
  7 |  8 | 2014-08-01
  1 |  2 | 2014-07-26
  2 |  3 | 2014-07-27
  3 |  4 | 2014-07-28
  4 |  5 | 2014-07-29
  5 |  6 | 2014-07-30
  6 |  7 | 2014-07-31
(7 rows)