which have a date column which looks like
That's not a date column, but a timestamp (date and time). Assuming type
timetamptz? (You should always disclose actual table definitions.)
WHERE timestamp_in_disguise::time >= '22:00'
OR timestamp_in_disguise::time <= '05:00';
WHERE timestamp_in_disguise::time NOT BETWEEN '05:00' AND '22:00';
The first includes bounds and you can adjust as needed.
The second uses
BETWEEN, which always includes bounds - so excludes them in the negated expression.
You can still use a hack to include bounds building on the inside knowledge that Postgres stores times and timestamps with microsecond resolution (6 fractional decimal digits) in its current implementation.
WHERE timestamp_in_disguise::time NOT BETWEEN '05:00:00.000001' AND '21:59:59.999999';
But I strongly advice against the latter. Building on implementation details is brittle and ugly.
Casting the string literals to the right is optional as their type is derived from the typed column.
If we are, in fact, dealing with
timestamptz you need to define where in the world it's supposed to be 22:00 etc. (You may need to think about that with
timestamp, too.) See:
BETWEEN and including lower and upper bound:
If you run this kind of queries a lot, consider an expression index:
CREATE INDEX ON tbl ((timestamp_in_disguise::time)); -- parentheses needed