How to take on time-registration?

Question

I was looking into StackExchange for a site for discussing just the basic ideas as I'm almost sure I come across it some time ago, but I couldn't find any out of beta, and the lack of developer friends to discuss in depth the issue, makes me come to StackOverflow for help, so here's my question.

events

As I have not much to do these days, I was looking at apps like Toggle and Clockify and I was wondering about those...

As a basic concept, a user can register types of events for any gap whatsoever, for example:

2017-12-28 00:00:00 ~ 2018-01-14 23:59:59 // vacation
2018-01-20 00:00:00 ~ 2018-01-20 23:59:59 // devOps event
2018-01-24 00:00:00 ~ 2018-01-24 12:00:00 // haircut

detailed info

if I want a detailed info about the events on the month of January, the actual conclusion I want to end up is something as

details = {
    events: [{...}, {...}, {...}],
    daysUsed: {
        vacation: 10, // holidays+weekends not counted
        internal: 1,
        personal: 0,5            
    }
}

problems on paper I faced

even if I "saved" the data as is, for example in an RDS table as

// evtId | user | from | to | eventType | description

1 | 1 | 2017-12-28 00:00:00 | 2018-01-14 23:59:59 | V | vacation
2 | 1 | 2018-01-20 00:00:00 | 2018-01-20 23:59:59 | I | devOps event
3 | 1 | 2018-01-24 00:00:00 | 2018-01-24 12:00:00 | P | haircut

I can't use datetime to actually select the range from Jan 1st to Jan 31st as

WHERE userId = 1 and from >= '...' and to <= '...'

I would need to spread each event into a dailyEvent manner in order to actually calculate anything regarding days, like

// eventId | date | evtType | dayType (holiday | weekend | vacation)

1 | 2017-12-28 | V | vacation
1 | 2017-12-29 | V | vacation
1 | 2017-12-30 | V | vacation & weekend
1 | 2017-12-31 | V | vacation & weekend
1 | 2018-01-01 | V | vacation & holiday
1 | 2018-01-02 | V | vacation
1 | 2018-01-03 | V | vacation
1 | 2018-01-04 | V | vacation
1 | 2018-01-05 | V | vacation
... 

with such data I can then easily use only the database to do some calculations, at least between the dates and then process times in app

I got to look at MomentJs library to see if I could, for example, add the raw registrations and somewhat get the results I wanted, but it's only for manipulating and calculate dates, not a range of dates ... a calendar library would make more sense ... didn't found any for javascript that I could see their approach to the problem...

two tables instead of one

I also thought about holding a secondary table where (dailyEvent) where I get a time gap and fill that table with one entry per day, making it easier to retrieve data from 2 dates...

the issue is that is one more table to maintain when the user edits/deletes the main event... and sync-stuff never worked that well :/

• what would be the best way to hold/retrieve data for such task?
• is the spreading into daily events the best idea? it would grow a table exponentially no? but nothing like a well designed index table wouldn't care about such, and maybe all events pass 5y old could be "archived" into a special table...

what is your take?

---

added

to make the question even simpler, this is what I'm trying to wrap my head into

how would one query this to get:

events 1, 2, 4 when query day 3
events 1, 2, 3 when query day 5
events 1, 2, 3 when query from days 4 to 7
events 1, 2 when query from days 10 to 14

here's a test ms-sql to play around:

Hostname    3b5a01df-b75c-41f3-9489-a8ad01604961.sqlserver.sequelizer.com   
Database    db3b5a01dfb75c41f39489a8ad01604961
Username    czhsclbcolgeznij    
Password    WrXF2Eozi2qWHAeYejFqRn8cPfQqZyh3FS2JteUHPZHnmoDhwxgVaeMJrVYUX6HR

Answer 1

In pseudo-sql, to find the intersection of time periods you can use

select 
     *
from events
where startdate<=[end of day 3]
and enddate>=[start of day 3] 

Answer 2

Well, the task can't be solved entirely in SQL (from desired output, I guess you are aware of that). Also, I won't give exact solution, as it might be too broad, but I'd be happy to help in case of any questions. I took a look at your account and I saw that you have golden badge in C#, so I will reference this language in case of any suggestions.

First, your desired object is quite complex, so let me assume such model:

public class Summary
{
    public List<string> events { get; set; }
    public Dictionary<char, decimal> daysUsed { get; set; } 
        = new Dictionary<char, decimal> { { 'I', 0 }, { 'V', 0 }, { 'P', 0 } };
}

Your first suggestion about storing events was better in my opinion and I've chosen such approach.

DDL

declare @table table (evtId int, userId int, startDate datetime, endDate datetime, eventType char(1), [description] varchar(1000))
insert into @table values
(1 , 1 , '2017-12-28 00:00:00' , '2018-01-14 23:59:59' , 'V' , 'vacation'),
(2 , 1 , '2018-01-20 00:00:00' , '2018-01-20 23:59:59' , 'I' , 'devOps event'),
(3 , 1 , '2018-01-24 00:00:00' , '2018-01-24 12:00:00' , 'P' , 'haircut')

Now, let's take a look at what you want to do:

first, declare period, for which you want the summary - it can be in C# and passed through some SQL methods or some ORM to DB. For simplicity, I used SQL:

declare @startDate datetime, @endDate datetime
select @startDate = '2018-01-01 00:00:00.000', @endDate = '2018-01-31 23:59:59.999'

Which is your example (period from 1st to 31st of Jan).

Now let's try to get information we need:

first, the most important part is to define CTE, which will perform tasks:

1. Get all events, which started or ended in given period (in January). Note: events that lie partially in given period would be also included.

2. We don't want any data from outside given period, so in CTE we will substitute start date, when event started before given period, with 1st of Jan. Also, we will substitute end date, when event ended after given period, with 31st of Jan.

The CTE:

;with cte as (
    select evtId,
           userId,
           case when startDate < @startDate then @startDate else startDate end [startDate],
           case when endDate > @endDate then @endDate else endDate end [endDate],
           eventType,
           [Description]
    from @table
    where (startDate between @startDate and @endDate) or
          (endDate between @startDate and @endDate)
)

Now, we have "clean" data from your table. Time to query it:

For the first part, i.e. events: [{...}, {...}, {...}], I guess following query will suffice:

select [description] from cte

Result of this query could be stored in events property of Summary class.

Now, we need to populate daysUsed property:

select eventType,
       round(1.0 * sum(datediff(minute, startDate, endDate)) / (60 * 24), 2) [daysUsed]
from cte
group by eventType

Above query will return following data:

eventType|daysUsed
I        |1
P        |0.5
V        |14

In general, result of above query will always be:

eventType|daysUsed
I        |value
P        |value
V        |value

This result can be easily used to populate daysUsed propertry in Summary class.

At the end, you can serialize object of Summary class to JSON. Then you'd have desired output.

Notes:

1. C# class is just an example to help me visualise what I want to do at every step. I don't know what technology you are using.

2. I know, that results of second query (to populate daysUsed) are little bit different from yours, as I didn't exclude weekends. I didn't exclude weekends and holidays, as that wasn't primary problem and I think you can easily handle that.

3. I usded CTE, which can be queried only once, so to be strict, it should be some temporary table or CTE's inner query can be used as a subquery in other queries (which would be the easiest in my opinion).

Answer 3

If I understand the question correctly you simply wish to return events that was active with in a specified period. If that is correct then this is a relatively simple problem to solve. We can consider an event active within a period if:

1. Either the starting edge or ending edge of the event is contained within the period or

2. The event spans the entire period in which case the starting edge of the event will be less than the period’s start time and the ending edge of the event will be greater than the period’s end time.

Following sample demonstrates:

-- -------------------------------------------------------
-- Creat sample table and data
-- -------------------------------------------------------
CREATE TABLE [dbo].[EventData](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [EventStart] [datetime] NOT NULL,
    [EventEnd] [datetime] NOT NULL,
 CONSTRAINT [PK_EventData] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[EventData] ON 

GO
INSERT [dbo].[EventData] ([ID], [EventStart], [EventEnd]) VALUES (1, CAST(N'2018-01-01T00:00:00.000' AS DateTime), CAST(N'2018-01-13T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[EventData] ([ID], [EventStart], [EventEnd]) VALUES (2, CAST(N'2018-01-03T00:00:00.000' AS DateTime), CAST(N'2018-01-15T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[EventData] ([ID], [EventStart], [EventEnd]) VALUES (3, CAST(N'2018-01-04T00:00:00.000' AS DateTime), CAST(N'2018-01-06T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[EventData] ([ID], [EventStart], [EventEnd]) VALUES (4, CAST(N'2018-01-02T00:00:00.000' AS DateTime), CAST(N'2018-01-04T00:00:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[EventData] OFF
GO

 -- -------------------------------------------------------
-- Sample Query
-- -------------------------------------------------------

DECLARE @periodStart DATETIME = '10 Jan 2018';  -- Selection period start date and time
DECLARE @periodEnd DATETIME = '14 Jan 2018'; -- Selection period end date and time

SELECT  *
FROM    [EventData] [E]
WHERE   (
            (   -- Check if event start time is contained within period
                (@periodStart <= [E].[EventStart]) AND (@periodEnd > [E].[EventStart])
            ) 
            OR
            (
                -- Check if event end time is contained within period
                (@periodStart < [E].[EventEnd]) AND (@periodEnd > [E].[EventEnd])
            )
            OR
            (
                -- Check if the event spans the entire period
                (@periodStart >= [E].[EventStart]) AND (@periodEnd <= [E].[EventEnd])
            )
        )