BETWEEN predicate

The special BETWEEN predicate is available as a convenience.

Syntax

WHERE a BETWEEN x AND y

Examples

WHERE a BETWEEN x AND y

is equivalent to:

WHERE a >= x AND a <= y

Similarly:

WHERE a NOT BETWEEN x AND y

is equivalent to:

WHERE a < x OR a > y

You can use the BETWEEN predicate for date ranges:

=> 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)

You can also use the NOW and INTERVAL keywords to select from a date range:

=> 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)