SQL - how to filter by timestamp looking for anything after a certain time on a previous day-CodePudding

I need to filter a query results to only bring in rows of data that were created after 5pm the previous BUSINESS DAY up until current time, with business day being Monday to friday. I can filter for previous business day, and I can filter by timestamp...but I can't combine the 2 in order to filter after 5pm previous business day.

So for timestamp filter the field is call 'create_ts' and I can filter for after 5pm like so: AND to_char(create_ts, 'HH24:MI:SS') >= '17:00:00'

And for previous business day I can filter like so: and a.create_ts >= trunc(prevbd(sysdate))

how do I filter a.create_ts for >= trunc(prevbd(sysdate) after 5pm?

CodePudding user response：

If prevbd is a function you've defined that returns a date from the previous business day (I'm guessing from the fact that you're calling trunc that it returns a date that is the current time on the previous business day which seems like a weird design choice), then just

a.create_ts >= trunc( prevbd(sysdate) )   interval '17' hour

to look for things after 5pm on the previous business day.

CodePudding user response：

You can use (without the need for a user-defined function):

SELECT *
FROM   table_name
WHERE  a.create_ts >= LEAST(
                        -- Yesterday
                        TRUNC(SYSDATE) - 1,
                        -- Friday of the week containing yeasterday
                        TRUNC(TRUNC(SYSDATE) - 1, 'IW')   4
                      )   INTERVAL '17' HOUR
AND    a.create_ts <  SYSDATE;