Postgresql query between date ranges

Power-up your query with PostgreSQL's date ranges and operators

PostgreSQL's advanced range types and operators allow for efficient queries within date ranges:

SELECT * FROM table_name
WHERE daterange(start_date, end_date) @> date_column;


Here, @> operator checks if the date_column fell within the specified daterange. This powerful technique is more optimal than using BETWEEN and fully utilizes PostgreSQL's range capabilities.

Effortlessly calculate dates with date_trunc

To effortlessly handle start and end dates, harness the power of the date_trunc function:

SELECT * FROM table_name
WHERE date_column >= date_trunc('month', CURRENT_DATE)
AND date_column < (date_trunc('month', CURRENT_DATE) + interval '1 month');


This query fetches rows for the current month, automating the start and end date handling without any manual input. You can now enjoy a cup of coffee instead!

Unleashing the power of BETWEEN SYMMETRIC

Occasionally, programmers (yes, even us!) might mix up the date order. To avoid this boo-boo, use BETWEEN SYMMETRIC:

SELECT * FROM table_name
WHERE date_column BETWEEN SYMMETRIC 'end_date' AND 'start_date';


Now PostgreSQL handles the argument swapping for you. Relax, and let PostgreSQL do the heavy lifting!

Optimizing index for speedy queries

To speed up your range queries, maintain index usage. PostgreSQL optimizes indexing such that it allows range seeks instead of time-consuming table scans:

CREATE INDEX ON table_name (date_column);


Don't mind those table scans, indexing has your back!

Dropping ‘excessive row calculations’ weight

Steer clear of DATEPART for year and month comparisons to avoid computation for each row. Focus on queries using >= and < for efficient index use, and leverage date_trunc function to group by year or month.