How to Query Data Between Specific Date Ranges in PostgreSQL

The time and date-specific data is very crucial for some use cases such as e-commerce stores. They can use daily generated data to get insights and perform data analysis with those insights. These insights and analyses can then be used to track the activity and performance of their products. In Postgres, we can generate data between two dates in order to get some insights. In this post, we will be discussing the methods to get the data between two specific dates.

How to Query Data Between Specific Date Ranges in PostgreSQL?

We can get the data between two specific date ranges by various methods that we are going to discuss in this post. For that consider the following table named “store_sales_details”:

The table contains the data of an e-commerce store. Suppose we have to find them for sales of products between the two dates i.e. '2023-05-20' and '2023-08-25'.

Let's see the method using which we can find the data between these two specified date ranges.

Method 1: Using WHERE and AND

We can use the WHERE and AND clauses to specify the data range and get the data between these two dates. The query will be written as:

SELECT * FROM store_sales_details
WHERE
date_of_order >= '2023-05-20'
AND date_of_order <= '2023-08-25';

In the above syntax, we have specified the date_of_order range using WHERE and AND keywords and using the relational/comparison operators. The resulting table contains the data between these two dates:

Method 2: Using BETWEEN

This method is almost the same as the above method with the addition of the BETWEEN keyword. The query for this method can be written as:

SELECT * FROM store_sales_details
WHERE
date_of_order BETWEEN '2023-05-20'AND '2023-08-25';

This query returns the data between the two specified date ranges using the BETWEEN keyword. The output looks like this:

Method 3: Using SYMMETRIC

The SYMMETRIC keyword is used with the BETWEEN keyword. This query works the same as the above method. The query can be written as:

SELECT * FROM store_sales_details
WHERE
date_of_order BETWEEN SYMMETRIC '2023-05-20' AND '2023-08-25';

The query will return the data between the two specified date ranges:

Method 4: Using Range Types

The range types are supported by Postgres from Postgres version 9.2 and above. We will use the date range for our use case. Let’s see how it works:

SELECT * FROM store_sales_details
WHERE '[2023-05-20, 2023-08-25]'::daterange @> date_of_order;

The above syntax:

● The date range is specified with the WHERE keyword.

● The query will return the data that lies between the specified range contained in the “date_of_order” column.

So the output of the query will be:

The above-mentioned were the methods to fetch the data between the specified date range in PostgreSQL. That was all for this topic.

Conclusion

We can get the data between two specified date ranges using the methods that we extensively discussed in this article. These methods include the “WHERE” and “AND” clauses, the BETWEEN operator, the SYMMETRIC keyword, and the Range data types. This data proves to be very beneficial for tracking and analysis purposes. We can easily get insights and metrics about any product to analyze its performance in the market.

How to Query Data Between Specific Date Ranges in PostgreSQL