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)