DATE is an important data type that stores calendar dates in PostgreSQL. Various built-in functions, operators, clauses, etc., are used in Postgres to store and manipulate the dates. For instance, the “BETWEEN” clause, the “DATE_TRUNC()” function, and the basic comparison operators like “=”, “!=”, “>=” etc., are used to compare the dates in Postgres.

This post presents an in-depth overview of comparing dates in Postgres using practical examples.

How to Compare Dates Using Comparison Operators?

The WHERE clause fetches the table’s data based on a particular condition. Use the basic comparison operators in the WHERE clause to compare various dates, as shown in the following syntax:

SELECT col_list
FROM tbl_name
WHERE date_col = specific_date;

Let’s understand the “=” operator via the following example.

Example 1: Comparing Dates Using = Operator

A sample table named “emp_data” with the following content:

SELECT * FROM emp_data
ORDER BY emp_id ASC;

Let’s fetch the employees' data whose joining date is “2019-01-15”:

SELECT * FROM emp_data
WHERE joining_date = '2019-01-15';

In this example, a specific date is compared with the “joining_date” column of the “emp_data” table using the “=” operator:

The “=” operator performs the date comparison and retrieves the data accordingly.

Example 2: Using AND Operator With Comparison Operators

In the following example, the AND operator is used in the WHERE clause to compare dates between a specific range:

SELECT * FROM emp_data
WHERE joining_date >= '2019-06-01' AND joining_date <= '2022-06-01';

The comparison operators “>=” and “<=” are used along with the AND operator to fetch the employees' data between a specific range:

The comparison operators compare the dates and retrieve the employees' data accordingly.

How to Compare Dates Using BETWEEN Clause?

The BETWEEN operator/clause compares the given dates and returns the data within a specific range:

SELECT * FROM emp_data
WHERE joining_date BETWEEN '2019-06-01' AND '2022-06-01';

The BETWEEN operator retrieves the employees' data within the specified dates.

How to Compare Dates Using DATE_TRUNC() Function?

The DATE_TRUNC() function can also be used to compare the dates based on a specific field, such as year, month, etc.

SELECT * FROM emp_data
WHERE DATE_TRUNC('YEAR', joining_date) = '2019-01-01';

The DATE_TRUNC() function compares the dates and returns the data based on the specified year.

Conclusion

In PostgreSQL, different built-in functions, operators, clauses, etc., are used to store and manipulate the dates. To perform the date comparison, the “BETWEEN” clause, the “DATE_TRUNC()” function, and the basic comparison operators like “=”, “!=”, “>=” etc., are used in Postgres. The AND & OR operators can be used in the WHERE clause to combine multiple conditions. A detailed analysis of the date comparison is provided in this post using various methods.