SQL Duration Calculation for Painting Progress

Now that I have the additional endpoint data working to track an individual painting’s progress, I need a query to return the total-duration from the ‘painting_status’, “start” to each day’s “in progress” and ending with the status “completed”. Here is the current basic structure from each of my two tables. NOTE: * denotes entries created via the IOS Shortcuts image-upload to the endpoint ‘/uploads’.

<progress>
id
uuid *
timestamp_for_DB *
image
working_title
final_title
painting_status (start, in progress, completed) *

<time_data>
id
uuid *
timestamp_for_DB *
created_at
the_date
time_in
time_out
duration
entry
notes

To calculate the total duration from “start” to “in progress” days and ending with “completed” for an individual painting, you can use a SQL query that joins the progress and time_data tables on the uuid field, and then aggregates the duration based on the painting’s lifecycle defined in painting_status.

This query will:

1. Identify the range of dates for each painting based on the “start” and “completed” statuses in the progress table.
2. Sum up the duration in the time_data table for all entries within that date range for each painting.

Here’s an example query that achieves this:

SELECT 
    p.working_title,
    p.final_title,
    SUM(TIME_TO_SEC(TIMEDIFF(td.time_out, td.time_in))) AS total_seconds
FROM 
    progress p
JOIN 
    time_data td ON p.uuid = td.uuid
WHERE 
    p.uuid IN (
        SELECT uuid FROM progress WHERE painting_status = 'start'
    )
    AND td.the_date >= (
        SELECT timestamp_for_DB FROM progress WHERE uuid = p.uuid AND painting_status = 'start'
    )
    AND td.the_date <= COALESCE(
        (
            SELECT timestamp_for_DB FROM progress WHERE uuid = p.uuid AND painting_status = 'completed'
        ),
        CURDATE() -- Assuming you want to include up to the current date if not completed
    )
GROUP BY 
    p.uuid

Breakdown of the Query:

• The subquery SELECT uuid FROM progress WHERE painting_status = 'start' identifies the UUIDs of paintings that have started.
• For each of these UUIDs, it finds the “start” date in the progress table and uses it as the lower bound of the date range.
• If there’s a “completed” status for the painting, it uses that date as the upper bound of the date range. If the painting isn’t completed yet (COALESCE function), it uses the current date (CURDATE()) as the upper bound.
• It then sums up the durations (duration column in the time_data table) for all records within this date range.
• The TIME_TO_SEC(TIMEDIFF(td.time_out, td.time_in)) calculates the difference in seconds between time_out and time_in for each entry in time_data, which are then summed up to get total_seconds.

Notes:

• This query assumes that time_in and time_out in the time_data table are of a time or datetime type and that duration can be calculated as the difference between these two fields.
• Ensure that the timestamp_for_DB in your progress table and the_date in your time_data table are compatible date types for comparison.
• The date comparison (td.the_date >= ... AND td.the_date <= ...) assumes that the_date represents the date portion of your timestamps. Adjust this part if the_date includes time components or if your date range logic differs.
• This example calculates the total duration in seconds. You might need to convert this to your preferred format (hours, minutes, etc.).

Please adjust the field names and logic as necessary to fit your exact database schema and requirements.