Postgres selecting current hour data

Question

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.

Olaf Dietsche · Accepted Answer · 2023-04-26 18:17:34Z

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')

Gordon Linoff · Accepted Answer · 2016-10-15 19:10:27Z

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'

Collectives™ on Stack Overflow

Postgres selecting current hour data

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related