How can I ensure postgresql records are between given start and end times when some are midnight?

Question

I have a table of venues

# == Schema Information
#
# Table name: venues
#
#  id                          :integer          not null, primary key
#  name                        :string           default(""), not null
#  address_1                   :string           default(""), not null
#  address_2                   :string
#  city                        :string           default(""), not null
#  county                      :string           default(""), not null
#  postal_code                 :string           default(""), not null
#  mon_start                   :time
#  mon_end                     :time
#  tue_start                   :time
#  tue_end                     :time
#  wed_start                   :time
#  wed_end                     :time
#  thu_start                   :time
#  thu_end                     :time
#  fri_start                   :time
#  fri_end                     :time
#  sat_start                   :time
#  sat_end                     :time
#  sun_start                   :time
#  sun_end                     :time
#  created_at                  :datetime         not null
#  updated_at                  :datetime         not null

Some of the venues close time is after midnight, and some close before midnight.

I want to find all venues that are currently open, in relation to the time now()

First attempt

SELECT "venues".* FROM "venues" WHERE (mon_start <= now()::time AND mon_end > now()::time)

This doesn't return records if mon_end is after midnight.

Second attempt

SELECT "venues".* FROM "venues" WHERE (mon_start <= now()::time AND mon_end + interval '1 day' > now()::time)

But this still doesn't return records if mon_end is after midnight.

Question

How can I select all venues where the mon_start is less than the time now, and the mon_end is greater than the time now.

e.g. Mon start: 4pm Mon end: 1am Now: 11:30pm

Bonus Question

Is there a way for me to query the correct column based on the current day name?

e.g. If today is wednesday, search on wed_start and wed_end

Answer 1

You need work with DATE

SQL DEMO

WITH time_table as (
    SELECT '10:30pm'::time as open_time, '01:30am'::time close_time
    UNION
    SELECT '09:00pm'::time as open_time, '11:30pm'::time close_time
)
SELECT open_time, close_time, now(),
       now()::date + open_time as open_time,
       CASE WHEN close_time > open_time
            THEN now()::date + close_time
            ELSE now()::date + close_time + INTERVAL '1 day'
       END as close_time
FROM time_table

OUTPUT