Question: Server Utilization Time
Given a table containing server start and stop timestamps for multiple servers, write a query to calculate the total uptime (in full days) for each server. Assume a server can start and stop multiple times.

Answer:

You have a table that records when a server started and when it stopped.
Each server can start and stop multiple times.
You need to calculate the total uptime in full days for each server.

Sample Data (server_logs):

server_idstart_timestop_time
A2024-01-01 08:00:002024-01-02 08:00:00
A2024-01-03 10:00:002024-01-05 09:00:00
B2024-01-02 00:00:002024-01-02 23:00:00

Step-by-Step Explanation:

Step 1: Find out how long each server was active during each start-stop period.

We calculate the difference between stop_time and start_time for each record, measured in hours.

Example:

  • Server A, first row:
    2024-01-01 08:00 to 2024-01-02 08:00 = 24 hours
  • Server A, second row:
    2024-01-03 10:00 to 2024-01-05 09:00 = 47 hours
  • Server B:
    2024-01-02 00:00 to 2024-01-02 23:00 = 23 hours

Step 2: Add up the total active hours for each server.

Now sum all hours for each server.

  • Server A total = 24 + 47 = 71 hours
  • Server B total = 23 hours

Step 3: Convert total hours into full days.

One full day = 24 hours.

Divide total hours by 24:

  • Server A: 71 ÷ 24 = 2.958 days → Only 2 full days (ignore the decimal part)
  • Server B: 23 ÷ 24 = 0.958 days → 0 full days

Expected Final Result:

server_idtotal_uptime_days
A2
B0

How to Write the SQL Query:

SELECT 
  server_id,
  FLOOR(SUM(TIMESTAMPDIFF(HOUR, start_time, stop_time)) / 24) AS total_uptime_days
FROM 
  server_logs
GROUP BY 
  server_id;

Calculates

  • TIMESTAMPDIFF(HOUR, start_time, stop_time) Calculates the number of hours between start and stop times.
  • SUM(...): Divides up all the active hours for each server.
  • FLOOR(... / 24) : Divides the total hours by 24 and keeps only the whole number part (full days).
  • GROUP BY server_id : Groups the calculation per server.

Important Points:

  • We only count full days. So if a server has 2.9 days, it still counts as just 2 days.
  • We must sum up all the active periods first, then divide by 24.
  • Always use FLOOR() or similar function to ignore fractions.