2

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_end set for id 5, meaning account is currently enabled.
  • Account was enabled once our time period started (id 2).

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
Carl
  • 149
  • 10
  • 1
    Does this answer your question? [MySQL: how to get the difference between two timestamps in seconds](https://stackoverflow.com/questions/3528219/mysql-how-to-get-the-difference-between-two-timestamps-in-seconds) Unclear that id 5 should return, perhaps have a `case when` for those edge cases. – user3783243 Apr 29 '22 at 15:22
  • Use the MYSQL internal function [TimestampDiff][https://dev.mysql.com/doc/refman/8.0/en/date-calculations.html]. – Markus Zeller Apr 29 '22 at 15:24
  • 1
    The referenced answers does not answer how to deal with open ended intervals, which is part of this question – Lennart - Slava Ukraini Apr 29 '22 at 15:40
  • You can use COALESCE to map null to a specific time. Something like: `select user_id , sum(TIMESTAMPDIFF(SECOND, enabled_start, coalesce(enabled_end, '2022-04-27 23:59:59'))) from t where enabled_start >= ... and enabled_end <= '2022-04-27 23:59:59' group by user_id` – Lennart - Slava Ukraini Apr 29 '22 at 15:44
  • What do one do when a dude marks your question as duplicate when it is not? – Carl May 03 '22 at 22:57

0 Answers0