select * from table_name where TIMESTAMP between trunc(sysdate-1) and
trunc(sysdate-1) + 6/24 or timestamp between trunc(sysdate-1) + 23/24 and
trunc(sysdate);

where yourColumn between trunc(sysdate -1) + 23/24 and trunc (sysdate) + 6/24

trunc(sysdate)           ->  today at 00:00 AM
trunc(sysdate-1)         ->  yesterday at 00:00 AM
trunc(sysdate-1) +  6/24 ->  yesterday at 06:00 AM
trunc(sysdate-1) + 18/24 ->  yesterday at 06:00 PM
...