How to select in the query a time range of 1 hour?

Question

SQL

select * from 
  history 
where
  clock == current_timestamp - interval '01:00:00'::timestamp;

DATA WITH FUNCTION to_timestamp

28689   2019-10-26 21:02:09 83.6353
28689   2019-10-26 21:01:39 83.6614
28689   2019-10-26 21:01:09 82.9015
28689   2019-10-26 21:00:39 82.9039

OUTPUT

Invalid input syntax

The desired output is a select where the clock column that is a timestamp is within a one hour range of the values that were entered.

Here is a previous answer which may help. https://stackoverflow.com/questions/12425853/postgresql-between-with-datetime — dswdsyd, Oct 28 '19 at 04:21

Tim Biegeleisen · Accepted Answer · 2019-10-28T04:26:09.337

1

You may try extracting the hour component from both your clock column and the current timestamp, and then comparing the difference between the two.

select *
from history 
where
    abs(extract(hour from clock) - extract(hour from current_timestamp)) <= 1;

If you instead want all records within the last hour, then try:

select *
from history
where extract(epoch from current_timestamp - clock) / 3600 <= 1;

edited Oct 28 '19 at 04:26

answered Oct 28 '19 at 04:21

Tim Biegeleisen

Your first solution solved my problem, I just had to sort the CLOCK column down, thank you very much! – Luis Henrique Oct 28 '19 at 04:30

1 Answers1