CODEWITHSUNDEEP
postgresql
Leena Verma
Leena Verma

Reputation: 15

How to select 24 hours old records using Postgres?

I am using this query select * from table_nm where table_nm_date > NOW() - INTERVAL '24 hour'

But giving today's records too. Please help me.

Output : "2016-03-20 19:31:11.896159",
         "2016-03-21 08:24:58.223245",
         "2016-03-21 09:13:59.768953",
         "2016-03-21 09:51:25.161428",
         "2016-03-21 11:35:07.378706"

I only want 2016-03-20 data.

Upvotes: 1

Views: 1788

Answers (2)

rpy
rpy

Reputation: 4013

Assuming table_nm_date is a usual date-time like data type then your query turns down to select "any entry from the last 24 hours"

If you want to exclude "todays" records you need to exclude those in an appropriate way, e.g by using table_nm_date between START_OF_WINDOW and END_OF WINDOW, setting both borders as it suits your needs.

Upvotes: 0

pozs
pozs

Reputation: 36224

If you want yesterday's data, filter for date only:

SELECT *
FROM   table_nm
WHERE  table_nm_date BETWEEN CURRENT_DATE - 1 AND CURRENT_DATE

(which is an index-friendly variant of:)

WHERE  table_nm_date::date = CURRENT_DATE - 1

Upvotes: 5

Related Questions