0

I'm trying to get the number of seconds a user has had his/her account enabled during a specific 7 day time period.

This can be easily done using TIMEDIFF - but what makes this tricky (and not answered anywhere else):

[A] The account was enabled once our time period started (id 2) and should be considered enabled since start date.

[B] There is no enabled_end set for id 5, meaning account is currently enabled and should be considered enabled until end date.

Her is the basic query:

SELECT SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(enabled_end, enabled_start)))) AS total_seconds from logs WHERE user_id = '123' and enabled_start >= '2022-04-22 00:00:00' and enabled_end <= '2022-04-27 23:59:59'

So above query will fail to include id 2 as described in [A] and id 5 as described in [B]

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

2 Answers2

0

Use

TIMEDIFF(LEAST(COALESCE(enabled_end, TIMESTAMP '2022-04-27 23:59:59'), TIMESTAMP '2022-04-27 23:59:59'))
  • If enabled_end is NULL then COALESCE returns range end.
  • If enabled_end is out of the range then LEAST returns range end.
  • In all other cases enabled_end value is returned.

TIMESTAMP specifier causes datetime compare. You may remove it - in this case the values will be compared as strings (in this case the datetime literal must be complete, i.e. it must contain leading zeros for 1-digit part values).

Akina
  • 39,301
  • 5
  • 14
  • 25
  • Thanks! Based on your inputs, I created this query that successfully handles the end date. Now how do we modify this to take into account the problem with start date as described in [A]? ```select sum(TIMESTAMPDIFF(SECOND, enabled_start, LEAST(COALESCE(enabled_end, TIMESTAMP '2022-04-27 23:59:59'), TIMESTAMP '2022-04-27 23:59:59'))) as total_seconds from logs where enabled_start >= '2022-04-22 00:00:00' and (enabled_end <= '2022-04-27 23:59:59'' OR enabled_end IS NULL) and user_id = '123' group by user_id``` – Carl May 31 '22 at 12:45
  • 1
    @Carl The same. But you must use GREATEST instead of LEAST and, if starting time cannot be NULL, do not need in COALESCE. – Akina May 31 '22 at 12:55
  • Thanks for your help @Akina - I just posted the query in full (taking into account other aspects) as a new answer. – Carl May 31 '22 at 22:06
0

As @Akina pointed out:

  • If enabled_end is NULL then COALESCE returns range end.
  • If enabled_end is out of the range then LEAST returns range end.
  • In all other cases enabled_end value is returned.
  • If enabled_start is out of the range then GREATEST returns range start.

However this didn't solve the issue in my question [A] that the account was enabled once our time period started (id 2) and should be considered enabled from the start date.

I found two different approaches to determine whether two date ranges overlap:

Also, if enabled_end is NULL, we only want to include it if enabled_start is less than the end date.

All together it resulted in this query (make sure dates are in the Y-m-d H:i:s format) - works like a charm!

SELECT sum(TIMESTAMPDIFF(SECOND, 
    
GREATEST(enabled_start, TIMESTAMP '2022-04-22 00:00:00'),
    
LEAST(COALESCE(enabled_end, TIMESTAMP '2022-04-28 00:00:00'), TIMESTAMP '2022-04-28 00:00:00'))
    
) as total_seconds FROM logs 

WHERE

((enabled_start <= '2022-04-28 00:00:00') and (enabled_end >= '2022-04-22 00:00:00') OR

enabled_end IS NULL and enabled_start < '2022-04-28 00:00:00')

and enabled_start IS NOT NULL
    
and user_id = '123' group by user_id
Carl
  • 149
  • 10