How to Compare Dates in Datetime Fields in PostgreSQL

1. Basic Date Comparison Operators in PostgreSQL
2. Use the < or > Operators to Compare Dates in Datetime Fields in PostgreSQL
3. Custom Query Modifications When Using Comparison Operators to Compare Dates in Datetime Fields in PostgreSQL
4. Use the RANGE Type to Compare Dates in Datetime Fields in PostgreSQL
5. Use the DATA TYPE FORMATTING Functions to Compare Dates in Datetime Fields in PostgreSQL
6. Use the BETWEEN Operator for Range Substitution to Compare Dates in Datetime Fields in PostgreSQL

Dates in PostgreSQL can be implemented either using timestamp, date, or time. The timestamp is a concatenation of date and time, while the date is represented in the format; YYYY-MM-DD.

In our last article, we read about how we could manipulate the timestamp and add and subtract days, hours, months, and years from it in PostgreSQL. Today we will be looking at the comparison operators for DATE types and see how we can use them to our benefit.

Basic Date Comparison Operators in PostgreSQL

PostgreSQL has a defined set of inputs for its DATETIME or timestamp format. They can be viewed in the table provided below:

Whereas TIME can have an input in all of the following syntaxes:

And for timestamp, you can use the following syntax:

TIMESTAMP '2019-01-01'

Remember that date can be compared to all other DATE types but can only be contrasted with similar TYPES. Different ways can be implemented for comparison.

For instance, you could even use the OVERLAP() function defined by the PostgreSQL documentation to check for overlapping dates and return a TRUE or FALSE.

Now let us go ahead and understand the various operators that we can use to compare the two dates.

Use the < or > Operators to Compare Dates in Datetime Fields in PostgreSQL

A straightforward query for comparison can be as follows:

SELECT '2021-01-01' < '2022-01-01'

The above will return a value TRUE.

SELECT '2021-01-01' > '2022-01-01'

You can also use other comparison operators, such as; <=, >=, and =.

If you use <> or !=, which stands for NOT EQUAL, the above will return TRUE as both dates are not similar.

The PostgreSQL documentation states that Comparison operators are available for all data types. And you cannot compare more than two dates as the result of the first comparison will return a BOOL value.

And a BOOL value cannot be compared with a DATETIME type or any other types representing DATE and TIME.

The operators above also take into consideration the TIME aspect. If you do the following:

select '2021-01-01 08:08:08' < '2021-01-01 10:01:01'

It will again return TRUE, which is correct as the TIME on the former DATE is less than the latter.

You can also use the IS DISTINCT and IS NOT DISTINCT operators as follows:

expression IS DISTINCT FROM expression
expression IS NOT DISTINCT FROM expression

This equals the NOT EQUAL or EQUAL operator but is an alternative. However, this will return FALSE if there are NULL dates and TRUE if just one is NULL.

Custom Query Modifications When Using Comparison Operators to Compare Dates in Datetime Fields in PostgreSQL

Suppose we have a timestamp 2021-01-01 08:08:08, and we want to compare this to 2021-01-01.

Using the following query:

select '2021-01-01 08:08:08' <= '2021-01-01'

This should return TRUE, but it happens to return FALSE.

Why? Because when writing 2021-01-01 itself, it means the midnight of 31st of December, 2021, meaning something like this: 2020-31-31 23:59:59.

The timestamp 2021-01-01 08:08:08 is equal or less because our timestamp is 9 hours advanced from the DATE we are comparing.

To remove this exception, we must tell our PostgreSQL server not to put a TIME in our DATE string automatically. We can use a CAST to the DATE type to solve this.

Why? Because PostgreSQL lists DATE as follows:

date	4 bytes	date (no time of day)

This means that it won’t include TIME. So now you can go ahead and use the following query, which will run perfectly fine.

select '2021-01-01 08:08:08' <= '2021-01-01'::date

or the following:

select '2021-01-01 08:08:08'::date <= '2021-01-01'

Use the RANGE Type to Compare Dates in Datetime Fields in PostgreSQL

So what are RANGES in PostgreSQL? A RANGE, as you may have guessed by the word, represents a range of values of any data type present.

RANGES include the following configurations as well:

tsrange - Range of timestamp without time zone

tstzrange - Range of timestamp with time zone

daterange - Range of date

Suppose you run the following query;

select '[2021-01-01,2021-01-01]'::tsrange

This will return something like this:

Let’s understand how this works first and then modify it for our DATE comparisons.

The brackets inside the RANGE are known as EXCLUSIVE and INCLUSIVE bounds. EXCLUSIVE bounds mean a bracket like this; (or) and INCLUSIVE stand for brackets like this; [or].

If you write using the EXCLUSIVE bracket, it will exclude the following value from the RANGE. So if we have something like (3,5), it will return a range as [4].

But if we have; [3,5), it will now return [3,4], and if (3,5], it will return [4,5].

So if we want to see the range between two dates, we can use the query above. But how does it work with comparing dates?

Let’s suppose that we want to check if 2020-12-31 is less than 2021-01-01. So we can write something like this:

select '[2020-12-31,2021-01-01]'::tsrange

It will tell us if there were any dates within the range of both and whether they are in order or not. This means that 2021-12-30 was less than 2021-01-01 or a date later than the latter in the RANGE.

And if we reverse this and run the query as follows:

select '[2021-01-01,2020-12-31]'::tsrange

You will notice an error:

Output:

ERROR:  range lower bound must be less than or equal to range upper bound
LINE 1: select '[2021-01-01,2020-12-31]'::tsrange

This tells us that the former DATE isn’t less than the latter. And so we can use this for comparison. We hope we don’t need to tell you about TSRANGE because it has been mentioned above and other types.

The error would be reproduced again if you’d try to run the following:

select '[2021-01-01 09:09:10,2021-01-01 09:09:09]'::tsrange

Instead of TSRANGE, you can even use the DATERANGE CAST.

Use the DATA TYPE FORMATTING Functions to Compare Dates in Datetime Fields in PostgreSQL

Another significant query for comparison that tends to be similar to the ones provided in the first solution is written as follows:

Select to_date(to_date('2018-03-26','YYYY-MM-DD')::text,'YYYY-MM-DD'::text) =
to_timestamp('2018-03-26', 'YYYY-MM-DD')

So what is happening here? We compare a DATE type with a timestamp.

This is possible as DATE can be compared with all other types of DATES. So the function TO_DATE returns a DATE type because it uses the parameters as (text, text).

The first call of TO_DATE converts the multiple TEXTS to a DATE type which is then cast to a TEXT again for the outer TO_DATE function.

PostgreSQL tends to return an error for the second parameter as it somehow confuses the STRING and calls it UNKNOWN rather than TEXT. Hence we added an EXPLICIT TYPECAST to it as well.

The rest is pretty easy to understand and will serve our purpose well.

Use the BETWEEN Operator for Range Substitution to Compare Dates in Datetime Fields in PostgreSQL

Using a query as follows:

select '2021-01-01' between '2020-01-01' and '2022-01-01'

or the one below:

select '2021-01-01' not between '2020-01-01' and '2022-01-01'

The BETWEEN operator returns TRUE if it is between, and NOT BETWEEN returns FALSE if it is not in the middle of the ranges provided.

So today, we learned about the different ways we can implement the use of operators to compare various DATE types. We hope you explore them further on your own, even though we have tried our best to cover all solutions as much as we can.

But technology keeps growing and expanding, and sooner or later, newer, better functions will replace the ones given above.