An In-Depth Introduction to SQL Date Range

By Community / Developer
Nov 15, 2023

Navigate to:

When it comes to database queries, dates are everywhere. It’s virtually impossible to avoid them, and why would you, since they bring so much usefulness to the table? (Pun not intended.)  More specifically, if you need to find data within a given timeframe, then an SQL date range is what you need.

In this post, we’ll take you on a SQL date range trip. You’ll understand what “SQL date range” means, the value it brings to your database querying, and how to use it to your benefit.

We’ll open by covering some prerequisites to make sure we’re on the same page and ready to start. After that, we dive right into the pressing questions: what a SQL date range is and why it’s useful. Then, we’ll take a brief diversion to do an overview of the main date and time data types used in SQL.

With that out of the way, we’ll get to the heart of the post, in which we’ll teach you how to use date ranges in practical ways by answering common questions about the topic.

By the end, you’ll not only have a solid understanding of SQL date ranges but also know how to use it in practice to make your queries better. Let’s dig in!

Prerequisites

To get the most out of this post, you’ll need the following:

  • a basic level of knowledge of databases and the SQL language
  • a PostgreSQL instance installed locally—the easiest way to accomplish this is to use the official Docker image
  • a database client able to connect to your PostgreSQL instance—for example, pgAdmin

Do you have all you need? Then let’s get started.

What Is a SQL Date Range? Why Should You Care?

What do we mean by “SQL date range”? It’s exactly what the name suggests: a range of dates in the SQL language.

A common scenario in many business processes is needing to fetch data that belongs to a specific timeframe. You might need to get data on employees that were hired during a given month. Perhaps the VP of sales needs revenue data from Q2. You get the picture.

With the help of date ranges, we can query data from a given period. For the next step, let’s do a brief overview of the data types related to time and data.

Date and Time Types in SQL: An Overview

Since we’re using PostgreSQL as the RDBMS for our examples, let’s review some of the data types it offers for handling date/time concerns:

  • TIME: This type is used to store just times.
  • DATE: Use this type if you need to store just dates.
  • TIMESTAMP: This type stores both the date and the time.
  • TIMESTAMP WITH TIME ZONE: The same as TIMESTAMP, but also stores the time zone.
  • DATERANGE: As the name suggests, this type stores a date range.

As you can see, in PostgreSQL there’s a data type dedicated to date ranges, which is extremely convenient for storing and querying ranges.

SQL Date Range: A Guide, in Questions

Having covered the “what” and “why” of date ranges in SQL, let’s, at last, cover the “how.” This section will follow a Q&A format: we’ll list common questions related to date ranges and answer them in a practical way. A white rectangular sign with blue text Description automatically generated

How Do I Select Date Ranges in SQL?

How will we select values that fall within a given timeframe? Let’s first create a table:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created DATE
);

Then, let’s insert some data:

INSERT INTO users (username, email, created)
VALUES
    ('JohnDoe', '[email protected]', '2021-06-15'),
    ('JaneSmith', '[email protected]', '2022-09-20'),
    ('MichaelJohnson', '[email protected]', '2022-03-10'),
    ('EmilyWilliams', '[email protected]', '2023-01-05'),
    ('DavidBrown', '[email protected]', '2023-07-18'),
    ('JenniferMiller', '[email protected]', '2019-02-28'),
    ('ChristopherDavis', '[email protected]', '2019-08-12'),
    ('JessicaWilson', '[email protected]', '2021-11-03'),
    ('DanielMoore', '[email protected]', '2022-04-25'),
    ('LindaTaylor', '[email protected]', '2019-05-17');

For our first example, we want to select users that were created in the first semester of 2022. For that, we can use the BETWEEN keyword:

SELECT * FROM users 
WHERE created BETWEEN '2022-01-01' AND '2022-06-30';

Bear in mind that BETWEEN is inclusive on both ends, which means that users whose creation dates were either January 1 or June 30 will be included in the results.

How Do I Select Date Ranges in SQL? Version 2

Let’s now see a more advanced example. Consider the following table that stores data for multiple-day events:

CREATE TABLE Events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    start_date DATE,
    end_date DATE
);

Let’s insert a few rows into it:

INSERT INTO Events (name, start_date, end_date)
VALUES
    ('Conference A', '2023-09-15', '2023-09-18'),
    ('Workshop B', '2023-10-05', '2023-10-07'),
    ('Seminar C', '2023-08-20', '2023-08-22'),
    ('Exhibition D', '2023-11-10', '2023-11-12'),
    ('Training E', '2023-09-25', '2023-09-28'),('Workshop F', '2023-10-16', '2023-10-19'),('Conference G', '2023-09-16', '2023-12-23');

Now, suppose we want to retrieve all events that are taking place during a giving period—let’s say, from October 15 to November 15. How would we go about that?

First, consider that we have several possibilities regarding events happening during the desired period:

  • the start and end dates of the event are within the desired period
  • one of the dates falls within the range, but the other doesn’t
  • the desired period is within the start and end dates of the event

In order to get all of the events we want, there are essentially two approaches:

  • list all possible scenarios
  • exclude the invalid scenarios

The second alternative is the easiest one. We just have to make sure we exclude the events whose start dates are after the end date of the desired period or whose end dates are before the start date of the desired period:

SELECT * FROM Events 
WHERE NOT (start_date > '2023-11-15' OR end_date < '2023-10-15')

We can also revert the query above in order to make it more readable:

SELECT *
FROM Events
WHERE start_date <= '2023-11-15' AND end_date >= '2023-10-15';

How Do I Select Date Ranges in SQL? Version 3

Let’s revisit the previous example, with a twist. First, let’s create a new version of the Events table, but this time using the DATERANGE type instead of two columns:

CREATE TABLE EventsWithRange (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    period DATERANGE
);

Then, let’s insert the same rows as before:

INSERT INTO EventsWithRange (name, period)
VALUES
    ('Conference A', '[2023-09-15, 2023-09-18]'),
    ('Workshop B', '[2023-10-05, 2023-10-07]'),
    ('Seminar C', '[2023-08-20, 2023-08-22]'),
    ('Exhibition D', '[2023-11-10, 2023-11-12]'),
    ('Training E', '[2023-09-25, 2023-09-28]'),
    ('Workshop F', '[2023-10-16, 2023-10-19]'),
    ('Conference G', '[2023-09-16, 2023-12-23]');

How would we go about writing the same query as before? As it turns out, it’s way easier now:

SELECT *
FROM EventsWithRange
WHERE period && '[2023-10-15, 2023-11-15]';

In the query above, we use the && operator to check whether the specified range (expressed by ‘[2023-10-15, 2023-11-15]’) overlaps with the event_period column. Yes, this syntax might not be intuitive if you aren’t used to it, but it does allow for a simpler query.

How Do I Compare Date Ranges in SQL?

When handling columns of type DATERANGE, we can use comparison operators as usual. For instance, let’s query events whose periods exactly match the desired range:

SELECT name
FROM EventsWithRange
WHERE period = '[2023-09-15, 2023-09-18]';

Let’s get an event whose time periods are larger (have more days) than the specified range:

SELECT *
FROM EventsWithRange
WHERE period > '[2023-09-15, 2023-09-16]';

Wrapping Up

Working with dates is an inevitable part of life when writing SQL queries. SQL date ranges are a specific facet of that, as well as a tricky one—even though date ranges are everywhere, it can be quite easy to get confused when writing queries against date ranges, which then results in bugs.

Luckily, PostgreSQL can help us with that with its DATERANGE type. This type dramatically simplifies the usage of SQL date ranges, resulting in simpler queries that are less likely to contain errors.

Additional SQL resources

This post was written by Carlos Schults. Carlos is a consultant and software engineer with experience in desktop, web, and mobile development. Though his primary language is C#, he has experience with a number of languages and platforms. His main interests include automated testing, version control, and code quality.