CODEWITHSUNDEEP
sqlpostgresqldatetimebetween
Singa Selva Sankar M
Singa Selva Sankar M

Reputation: 105

Select data between hour by hour basis

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

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

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.

Demo

Upvotes: 1

Related Questions