How to compare dates in datetime fields in Postgresql?

3.7K Asked by Dannasahi in Devops , Asked on Jul 1, 2021

I have been facing a strange scenario when comparing dates in PostgreSQL(version 9.2.4 in windows). I have a column in my table say update_date with type 'timestamp without timezone'. Client can search over this field with only date (i.e: 2013-05-03) or date with time (i.e: 2013-05-03 12:20:00). This column has the value as the timestamp for all rows currently and has the same date part(2013-05-03) but the difference in time part.

When I'm comparing over this column, I'm getting different results. Like the followings:

select * from table where update_date >= '2013-05-03' AND update_date <= '2013-05-03' -> No results
select * from table where update_date >= '2013-05-03' AND update_date < '2013-05-03' -> No results
select * from table where update_date >= '2013-05-03' AND update_date <= '2013-05-04' -> results found
select * from table where update_date >= '2013-05-03' -> results found

My question is how can I make the first query possible to get results, I mean why the 3rd query is working but not the first one?

Why use Postgre date comparison?

Can anybody help me with this? Thanks in advance.

Answered by Augustina Fuentes

It seems like you prefer the first form because you want to avoid date manipulation on the input string.

Use the below query:

SELECT *

FROM table

WHERE update_date >= '2013-05-03'::date

AND update_date < ('2013-05-03'::date + '1 day'::interval);

Note: PostgreSQL compare date is used to compare date between two different dates, which we have used as an input. We can compare the date by using where and between clauses; we can also compare the date using the date_trunc function in PostgreSQL.

Your Answer

Answer (1)

Ranjana

In PostgreSQL, comparing dates in datetime fields can be done using several methods and operators. Here are some common approaches:

1. Using Comparison Operators

PostgreSQL supports standard comparison operators such as <, <=, >, >=, =, and != for comparing dates.

Example:

SELECT *

FROM your_table

WHERE date_column > '2023-05-22';

2. Using the BETWEEN Operator

The BETWEEN operator can be used to check if a date falls within a specific range.

Example:

SELECT *

FROM your_table

WHERE date_column BETWEEN '2023-01-01' AND '2023-12-31';

3. Using the AGE Function

The AGE function can be used to calculate the interval between two dates, which can then be compared.

Example:

SELECT *

FROM your_table

WHERE AGE(NOW(), date_column) > INTERVAL '1 year';

4. Extracting Date Parts

You can extract parts of a date (like year, month, day) and compare them.

Example:

SELECT *

FROM your_table

WHERE EXTRACT(YEAR FROM date_column) = 2023;

5 Months