How to deal with datetime ranges in a table with separate columns for date and time?

In a table that stores events, the date and time are two separate columns:

CREATE TABLE events (
    pk int serial, 
    detail text, 
    ev_date date, 
    ev time without time zone
);

If I filter for dates:

ev_date BETWEEN start_date::date AND end_date::date AND 
ev_time BETWEEN start_date::time AND end_date::time

I am missing all events within the two dates that occurred outside the time range of every day.

Thus, the start time is only relevant for the start date and the same for the end date.

Anyone with an advice on how to do it efficiently for millions of events?

You can use the simple condition:

WHERE (ev_date, ev_time) BETWEEN (start_date, start_time) 
                             AND (end_date, end_time)

or this one:

WHERE ev_date + ev_time BETWEEN start_date + start_time
                            AND end_date + end_time

What's the best of these two depends on what indexes you have on the table. If you have an index on (ev_date, ev_time) use the first. If you can add a filtered index on (ev_date + ev_time), use the second.

*: I'm assuming that all these are parameters: start_date, start_time, end_date, end_time.

StackExchange Database Administrators Q#129751, answer score: 8