How to compare dates in datetime fields in Postgresql?

Asked by Danna Sahi Jul 1, 2021 10.8K views 2 answers

About this question

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.

Your answer

2 Answers

Best Answer · By JanBask DevOps Expert

Danna Sahi

Answered on Jul 1, 2021

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.

Was this helpful?

Ranjana Admin JanBask Expert Latest answer

Answered on May 22, 2024

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;

Was this helpful?

More Devops discussions

How to rename Docker images without rebuilding them 3 answers · 116.9K views rejected non fast forward-Git push rejected “non-fast-forward” 3 answers · 100.6K views The following untracked working tree files would be overwritten by merge, but I don't care 3 answers · 52.8K views Unable to resolve this error javac is not recognized as an internal or external command 5 answers · 31.9K views Git push: fatal: 'origin' does not appear to be a git repository - fatal Could not read from remote repository. 1 answer · 31.2K views What is the difference between pull and clone in git? 2 answers · 26.2K views

How to compare dates in datetime fields in Postgresql?

About this question

Your answer

2 Answers

More Devops discussions

Learn & Explore

Popular Tutorials

DevOps Interview Q&A

Latest DevOps Blogs

Want answers to more DevOps questions?

Stay in the loop — it’s free

You’re on the list!