CODEWITHSUNDEEP
postgresql
sanu
sanu

Reputation: 1068

Postgres selecting current hour data

I have a postgres database with a table 'token' and it has 'token_id' and its generated time

token_id | generated_time
196618   | 2016-10-15 01:02:48.963
196619   | 2016-10-15 01:02:50.569
196620   | 2016-10-15 01:03:12.931
196621   | 2016-10-15 02:03:17.037
196622   | 2016-10-15 02:22:55.782
196623   | 2016-10-15 02:24:57.477
196624   | 2016-10-15 03:23:00

What I want to do is selecting current hour data from the table for example if current datetime is 2016-10-15 01:30:15 then i want to select all the data between 2016-10-15 01:00:00 and 2016-10-15 02:00:00

thank you for any suggestions.

Upvotes: 2

Views: 6010

Answers (2)

Olaf Dietsche
Olaf Dietsche

Reputation: 74098

I would say date_trunc('hour') might be of help here, e.g.

select token_id, generated_time
from token
where generated_time between date_trunc('hour', current_timestamp)
    and date_trunc('hour', current_timestamp + interval '1 hour')

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use date_trunc():

where generated_time >= date_trunc('hour', current_timestamp)

That actually assumes no future times in your table. A more specific answer is:

where generated_time >= date_trunc('hour', current_timestamp) and
      generated_time < date_trunc('hour', current_timestamp) + interval '1 hour'

Upvotes: 2

Related Questions