Reputation: 105
I am using postgresql, My idea is to select the data which are created before 24 hour, which I retrieved using the following
SELECT * FROM table
WHERE created < now() - '24 hours'::interval
In this I tried to add a condition Which brings the data between the hour basis using the BETWEEN clause, for this my query is following
SELECT * FROM table
WHERE created < now() - '24 hours'::interval
AND created BETWEEN {condition} AND {condition}
My created column is in timestamp with timezone, When I tried to use
extract(hour from created)
in the condition, I am not getting any results. I don't know where I am doing wrong. Is there any other way to achieve this?
Any help would be appreciated.
Upvotes: 0
Views: 930
Reputation: 522817
If you want to check hourly ranges which occurred outside the last 24 hours, then use something like this:
SELECT *
FROM yourTable
WHERE created < now() - '24 hours'::interval AND
EXTRACT(hour from created) >= 10 AND EXTRACT(hour from created) < 11;
This would find all records outside of the last 24 hours whose hour is between 10 and 11am.
Upvotes: 1