Get data from the past "x" hours excluding the weekend

Question

I'm using Oracle 11g.

What I would like to do is select login data from the past (let's say) 10 hours. I would like to exclude the weekend from this, and let my query wrap around to the previous Friday. For example, if it's Monday at 8:00 AM, I want to be able to find the logins that occurred from 12-8 AM Monday, as well as data from 10-11:59 PM on Friday.

My current statement looks like this:

select * from logins where end_time >= sysdate - (10) / (24)

But I'm not sure how to exclude Saturday and Sunday.

There's no simple answer here. You have to write out the logic. Often the problem is solved with a calendar table, which you must maintain. — Joel Coehoorn, Aug 04 '22 at 14:13
Shoot, ok. I only have read-only access to the database, so maybe I'll just get the past 3 days and sort it all out in the backend. — William Convertino, Aug 04 '22 at 14:23
If your database does not have a dates/calendar table you can create your own on the fly and you can do this without the use of a Recursive CTE which is often necessary due to CTE's having a max recursion limit of 100 recursions. https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function — Code Novice, Aug 04 '22 at 14:45

Code Novice · Answer 1 · 2022-08-04T15:19:10.620

I didn't see what Database as the Question was only tagged as SQL. Regardless the answer below is SQL Server but the concept will work in any database. I've used the method shown below in SQLite and Oracle.

Get a list of dates between two dates using a function

There is an excellent answer contained in the above SO Post I provided above. However this question has so many answers and the excepted answer is actually not the answer that solved my issue when I originally had a similar calendar table problem on my end.

I've pasted below the answer that works without having to create a calendar table and works within any standard query which can then be turned into a View/Stored Proc/Function etc.... And since the SQL below does not use a recursive CTE you can use this code in Views, Stored Procedures, Functions... etc... The dates table is still populated using a CTE but it does this using an exponential method of joining in several fake tables. I think it is brilliant and it performs well.

DECLARE @StartDate  DATE = '08/01/2021'
    , @EndDate      Date = '08/01/2022'
;

-- From http://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function
-- it basically just selects a whole bunch of 1s, gets the ROW_NUMBER() for each and filters.
-- This seems to perform better than inserting a bunch of rows in a WHILE loop or a recursive CTE
-- just UNIONing to the next date.
WITH
        N0 AS (SELECT 1 AS n UNION ALL SELECT 1)
    ,N1 AS (SELECT 1 AS n FROM N0 t1, N0 t2)
    ,N2 AS (SELECT 1 AS n FROM N1 t1, N1 t2)
    ,N3 AS (SELECT 1 AS n FROM N2 t1, N2 t2)
    ,N4 AS (SELECT 1 AS n FROM N3 t1, N3 t2)
    ,N5 AS (SELECT 1 AS n FROM N4 t1, N4 t2)
    ,N6 AS (SELECT 1 AS n FROM N5 t1, N5 t2)
    ,nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS num FROM N6)
SELECT DATEADD(day, num - 1, @StartDate) AS TheDate
    , DATENAME(WEEKDAY,   (DATEADD(day, num - 1, @StartDate))) AS TheDayName
    , num - 1 AS TheOffset
FROM nums
WHERE num <= DATEDIFF(day, @StartDate, @EndDate) + 1

In the above SQL I added the field for TheDayName so that you can filter out the weekends however you can add so many more fields that pull in more details of the dates being pulled in your query. Refer to the below SQL as it shows how to add all of the unique date parts into the CTE as needed. Again I added one of the below fields to the above CTE to pull TheDayName.

/* The below shows how to obtain additional information a date */
-- https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

DECLARE @d DATE = '07/26/21';

SELECT
    TheDate         = CONVERT(date, @d),
    TheDay          = DATEPART(DAY,       @d),
    TheDayName      = DATENAME(WEEKDAY,   @d),
    TheWeek         = DATEPART(WEEK,      @d),
    TheISOWeek      = DATEPART(ISO_WEEK,  @d),
    TheDayOfWeek    = DATEPART(WEEKDAY,   @d),
    TheMonth        = DATEPART(MONTH,     @d),
    TheMonthName    = DATENAME(MONTH,     @d),
    TheQuarter      = DATEPART(Quarter,   @d),
    TheYear         = DATEPART(YEAR,      @d),
    TheFirstOfMonth = DATEFROMPARTS(YEAR( @d), MONTH( @d ), 1),
    TheLastOfYear   = DATEFROMPARTS(YEAR( @d), 12, 31),
    TheDayOfYear    = DATEPART(DAYOFYEAR, @d)

Last if you are needing an example of how you JOIN back to a calendar table see the accepted answer in this SO Post: How to Calculate the Total Unique Days Employed for All Jobs - No overlap days counted twice

Get data from the past "x" hours excluding the weekend

1 Answers1