Search code examples
sql-serverconcept

How to take on time-registration?

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
Solution

  • 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]