Filtering Data by Date Range in PostgreSQL using BETWEEN

Dates are a common data type used in databases to represent time information. In PostgreSQL, there are several useful functions and operators to work with dates and filter data by specific date ranges. One helpful operator for this is BETWEEN.

In this guide, we‘ll explore how to use BETWEEN for querying data between a start and end date in PostgreSQL.

Overview of BETWEEN in PostgreSQL

The BETWEEN operator allows checking if a value is within a range. The basic syntax is:

expression BETWEEN low AND high

Where low and high represent the lower and upper bounds of the range.

BETWEEN is inclusive, meaning the bounds are included in the range. Essentially, BETWEEN x AND y is the same as writing x >= value AND value <= y. For date/time values, this means:

dateColumn BETWEEN ‘2022-01-01‘ AND ‘2022-12-31‘

Matches dates greater than or equal to January 1st, 2022 and less than or equal to December 31st, 2022. So it will match the entire year of 2022.

The examples below will demonstrate using BETWEEN with date/times in PostgreSQL.

Filtering by Date Range Without Time Component

For simpler date filtering without time values, the standard date type can be used:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  order_date DATE
);

Basic querying between two dates:

SELECT *
FROM orders
WHERE order_date BETWEEN ‘2020-01-01‘ AND ‘2020-12-31‘;

This will give all orders placed during the year 2020.

The date literals follow a ‘YYYY-MM-DD‘ format. This standard date formatting allows for proper range filtering and comparisons.

We can also use variables in the BETWEEN clause instead of hard-coded values:

SET start_date = ‘2020-01-01‘;
SET end_date = ‘2020-12-31‘;

SELECT *
FROM orders
WHERE order_date BETWEEN :start_date AND :end_date; 

Which provides the same results but allows configurable date parameters.

Querying Timestamp Ranges

For full date/times including hours, minutes, seconds etc. the timestamp type can be used instead:

CREATE TABLE tracking (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMP
);

Querying works the same as the previous date examples:

SELECT *
FROM tracking
WHERE event_time BETWEEN ‘2020-01-01 00:00:00‘ AND ‘2020-01-31 23:59:59‘;

This will filter all events from January 1st 2020 to January 31st, 2020 inclusive.

Note the start is ‘2020-01-01 00:00:00‘ and end is ‘2020-01-31 23:59:59‘. This ensures we get the entire day on January 31st by specifying the end time is the last second before midnight on Jan 31st.

We can also cast string dates without times to timestamps to simplify the syntax:

SELECT *
FROM tracking
WHERE event_time BETWEEN ‘2020-01-01‘::timestamp AND ‘2020-01-31‘::timestamp; 

Casting to timestamp adds the default time, so no need to specify the full time values.

Date Math for Dynamic Date Ranges

Hard-coding specific dates for filtering works, but for reusable queries we usually need relative date ranges.

PostgreSQL provides date math syntax to allow this. Some examples:

Get orders from the past 30 days:

SELECT *
FROM orders
WHERE order_date BETWEEN now() - ‘30 days‘::interval AND now();

Using now() - ‘30 days‘ gives the date 30 days ago from the current timestamp. So this filters the past 30 days dynamically.

We can also use the CURRENT_DATE variable instead of now() for just the current date without timestamp:

SELECT *
FROM orders
WHERE order_date BETWEEN CURRENT_DATE - ‘30 days‘::interval AND CURRENT_DATE;

Queries for any relative range are possible:

SELECT * 
FROM tracking
WHERE event_time BETWEEN now() - ‘1 year‘ AND now() - ‘6 months‘;

This will dynamically get events from 6 months ago to 1 year ago.

The examples show that BETWEEN works great with dates in PostgreSQL and along with date math allows querying flexible relative date ranges.

Putting it All Together: Report for Date Range Parameters

Say we have an application that requires getting sales data within a provided start and end date range. We want to build a query that takes parameters for:

• start_date – Start date for report range
• end_date – End date for report range

We can use everything covered so far to build a query for this:

CREATE FUNCTION sales_report (start_date DATE, end_date DATE) 
RETURNS TABLE (
    date DATE,
    total_sales NUMERIC
)
LANGUAGE sql AS 
$$
    SELECT order_date, SUM(amount)
    FROM orders
    WHERE order_date BETWEEN start_date AND end_date
    GROUP BY order_date;
$$;

Breaking this down:

• Defines a sales_report function that takes a start_date and end_date parameter
• Returns a table with date and total_sales columns
• Filters orders table with BETWEEN on user-supplied date range
• Sums the sales and groups by date

We can call this for any custom date period we want:

SELECT * FROM sales_report(‘2020-06-01‘, ‘2020-06-30‘);

The function handles querying for any range specified.

This is only one example, but shows how these concepts come together for a very reusable report.

Summary

Using PostgreSQL‘s BETWEEN operator along with date functions provides powerful abilities for filtering by date ranges:

• BETWEEN allows querying between two values (inclusive bounds)
• Works with date and timestamp types for different granularity
• Dynamic ranges possible combining with date math
• Can be implemented in parameterized functions/procedures for customizable reporting

Properly utilizing dates and BETWEEN makes many analysis tasks much easier. Date range filtering is essential for application development work as well.

There are many capabilities in PostgreSQL for working with temporal data. But understanding the basics covered here provides the building blocks for all types of powerful date-driven queries and procedures.