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]

CodePudding user response：

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).

CodePudding user response：

I have now figured out how to find if a given date range is inside another date range. 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. As this answer points out, a simple condition to find out if two segments [a, b] and [c, d] intersect each other is (a-d)*(b-c) <= 0.

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
        
((UNIX_TIMESTAMP(enabled_start) - UNIX_TIMESTAMP('2022-04-28 00:00:00'))
* (UNIX_TIMESTAMP(enabled_end) - UNIX_TIMESTAMP('2022-04-22 00:00:00')) <= 0

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