I'm trying to get the number of seconds a user has had his/her account enabled during a specific 7 day time period.
We store enabled_start and enabled_end along with the user_id in our database.
How many seconds did the user have his/her account enabled between 2022-04-22 00:00:00 - 2022-04-27 23:59:59?
What makes it complicated:
- There is no
enabled_endset forid5, meaning account is currently enabled. - Account was enabled once our time period started (
id2).
How does one deal with these open intervals?
I gave up, hopefully someone knows better!
| id | user_id | enabled_start | enabled_end |
|---|---|---|---|
| 5 | 123 | 2022-04-26 12:13:38 | NULL (=account is still enabled) |
| 4 | 123 | 2022-04-25 15:22:36 | 2022-04-25 17:32:11 |
| 3 | 123 | 2022-04-24 11:16:46 | 2022-04-25 05:10:08 |
| 2 | 123 | 2022-04-15 14:44:00 | 2022-04-23 10:58:53 |
| 1 | 123 | 2022-03-29 16:44:15 | 2022-04-04 11:22:39 |
| 0 | 123 | 2022-03-24 13:44:15 | 2022-03-25 09:11:39 |