Audio Duration Tracking

Objective

Use AIDB's DURATION function to query and analyze audio files by their length. Duration tracking is essential for content organization, playlist creation, and time-based filtering.

Step 1: Create Audio Files Table

Create a table for storing audio with duration tracking.

CREATE TABLE audio_content (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    audio_file AUDIO(MP3),
    content_type VARCHAR(50),
    duration_seconds INTEGER,
    duration_formatted VARCHAR(10),
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Sample Audio Content

Add sample audio content with various durations.

INSERT INTO audio_content (id, title, content_type, duration_seconds, file_size) VALUES
    (1, 'Quick Tip #1', 'tip', 45, 720000),
    (2, 'Quick Tip #2', 'tip', 62, 992000),
    (3, 'News Brief', 'news', 180, 2880000),
    (4, 'Interview Segment', 'interview', 900, 14400000),
    (5, 'Full Podcast Episode', 'podcast', 3600, 57600000),
    (6, 'Audiobook Chapter 1', 'audiobook', 1800, 28800000),
    (7, 'Meditation Session', 'wellness', 600, 9600000),
    (8, 'Music Playlist', 'music', 2700, 43200000),
    (9, 'Language Lesson', 'education', 1200, 19200000),
    (10, 'Comedy Routine', 'comedy', 450, 7200000);

Step 3: Format Duration Display

Update with formatted duration strings.

UPDATE audio_content
SET duration_formatted =
    CASE
        WHEN duration_seconds >= 3600 THEN
            (duration_seconds / 3600) || ':' ||
            CASE WHEN ((duration_seconds % 3600) / 60) < 10 THEN '0' ELSE '' END ||
            ((duration_seconds % 3600) / 60) || ':' ||
            CASE WHEN (duration_seconds % 60) < 10 THEN '0' ELSE '' END ||
            (duration_seconds % 60)
        ELSE
            (duration_seconds / 60) || ':' ||
            CASE WHEN (duration_seconds % 60) < 10 THEN '0' ELSE '' END ||
            (duration_seconds % 60)
    END;

SELECT title, duration_seconds, duration_formatted
FROM audio_content
ORDER BY duration_seconds;

Step 4: Extract Duration from Audio Files

Use DURATION function on actual audio data.

SELECT
    id,
    title,
    DURATION(audio_file) as extracted_duration
FROM audio_content
WHERE audio_file IS NOT NULL
LIMIT 5;

Step 5: Duration Categories

Categorize audio by duration length.

SELECT
    title,
    content_type,
    duration_seconds,
    CASE
        WHEN duration_seconds < 60 THEN 'Micro (< 1 min)'
        WHEN duration_seconds < 300 THEN 'Short (1-5 min)'
        WHEN duration_seconds < 900 THEN 'Medium (5-15 min)'
        WHEN duration_seconds < 1800 THEN 'Standard (15-30 min)'
        WHEN duration_seconds < 3600 THEN 'Long (30-60 min)'
        ELSE 'Extended (> 1 hour)'
    END as duration_category
FROM audio_content
ORDER BY duration_seconds;

Step 6: Filter by Duration Range

Find audio within specific duration ranges.

-- Quick content (under 5 minutes)
SELECT title, duration_formatted, content_type
FROM audio_content
WHERE duration_seconds < 300
ORDER BY duration_seconds;

-- Standard episodes (15-45 minutes)
SELECT title, duration_formatted, content_type
FROM audio_content
WHERE duration_seconds BETWEEN 900 AND 2700
ORDER BY duration_seconds;

-- Long form content (over 30 minutes)
SELECT title, duration_formatted, content_type
FROM audio_content
WHERE duration_seconds > 1800
ORDER BY duration_seconds DESC;

Step 7: Duration Statistics by Type

Analyze duration patterns by content type.

SELECT
    content_type,
    COUNT(*) as item_count,
    SUM(duration_seconds) as total_seconds,
    SUM(duration_seconds) / 60 as total_minutes,
    AVG(duration_seconds) as avg_seconds,
    MIN(duration_seconds) as min_seconds,
    MAX(duration_seconds) as max_seconds
FROM audio_content
GROUP BY content_type
ORDER BY total_seconds DESC;

Step 8: Create Playlist by Duration

Build a playlist with target duration.

-- Find content that fits in a 30-minute commute
SELECT
    title,
    content_type,
    duration_formatted,
    SUM(duration_seconds) OVER (ORDER BY duration_seconds) as cumulative_seconds
FROM audio_content
WHERE duration_seconds <= 1800
ORDER BY duration_seconds;

Step 9: Duration Distribution

Analyze the distribution of content durations.

SELECT
    CASE
        WHEN duration_seconds < 60 THEN '< 1 min'
        WHEN duration_seconds < 300 THEN '1-5 min'
        WHEN duration_seconds < 900 THEN '5-15 min'
        WHEN duration_seconds < 1800 THEN '15-30 min'
        WHEN duration_seconds < 3600 THEN '30-60 min'
        ELSE '> 1 hour'
    END as duration_bucket,
    COUNT(*) as content_count,
    SUM(duration_seconds) / 60 as total_minutes
FROM audio_content
GROUP BY
    CASE
        WHEN duration_seconds < 60 THEN '< 1 min'
        WHEN duration_seconds < 300 THEN '1-5 min'
        WHEN duration_seconds < 900 THEN '5-15 min'
        WHEN duration_seconds < 1800 THEN '15-30 min'
        WHEN duration_seconds < 3600 THEN '30-60 min'
        ELSE '> 1 hour'
    END
ORDER BY MIN(duration_seconds);

Step 10: Total Library Duration

Calculate total listening time in library.

SELECT
    COUNT(*) as total_items,
    SUM(duration_seconds) as total_seconds,
    SUM(duration_seconds) / 60 as total_minutes,
    SUM(duration_seconds) / 3600 as total_hours,
    SUM(file_size) / 1048576 as total_megabytes
FROM audio_content;

Step 11: Content Efficiency

Analyze content size relative to duration.

SELECT
    title,
    content_type,
    duration_seconds,
    file_size,
    file_size / duration_seconds as bytes_per_second,
    (file_size / duration_seconds) / 1024 as kb_per_second
FROM audio_content
WHERE duration_seconds > 0
ORDER BY bytes_per_second DESC;

Step 12: Duration-Based Recommendations

Find content matching a time slot.

-- Find content for a 10-minute break
SELECT
    title,
    content_type,
    duration_formatted
FROM audio_content
WHERE duration_seconds BETWEEN 480 AND 720
ORDER BY duration_seconds;

-- Find exactly 30-minute content (with 2-minute tolerance)
SELECT
    title,
    content_type,
    duration_formatted
FROM audio_content
WHERE duration_seconds BETWEEN 1680 AND 1920
ORDER BY ABS(duration_seconds - 1800);

Cleanup (Optional)

DROP TABLE IF EXISTS audio_content;

Expected Outcomes

• DURATION function extracts audio length
• Duration formatting displays readable times
• Category filters organize by length
• Statistics reveal duration patterns
• Playlist building respects time limits

Duration Categories

Key Concepts Learned

• DURATION function for audio analysis
• Duration formatting techniques
• Time-based filtering
• Statistical analysis by duration
• Playlist time management