Rounding time in Python in SQL

how to get round of last hour between last hour and exactly 1 hour before , for example if now the hour is 14:10 the anser i am looking fir is give me all record between 13-14:00, my code is only for last hour :

select *
from my_table p
where p.when_seen between unix_timestamp(now())- 3600 and unix_timestamp(now())

wheen_seen is linux timestamp for example

select when_seen ,from_unixtime(when_seen ) from my_table LIMIT 1;

1539085264 2018-10-09 11:41:04

Answer

Divide the Unix timestamp by the number of seconds an hour has (3600), floor that division and multiply it by the number of seconds an hour has. Then oyu have the timestamp of the hour without minutes or seconds.

SELECT *
       FROM my_table p
       WHERE p.when_seen >= floor(unix_timestamp(now()) / 3600) * 3600 - 3600
             AND p.when_seen < floor(unix_timestamp(now()) / 3600) * 3600;

You should also considering using a right open interval as the right boundary isn’t part of the hour before.