How to Filter Data by Timestamp Ranges for Trend Analysis in Bambu Lab Filament Stock Data

Use DuckDB's read_parquet function with BETWEEN clauses on ISO 8601 timestamp strings, or fetch the manifest.json index to programmatically select Parquet shards from the Cloudflare R2 dataset.

The Bambu Lab Store Filament Tracker publishes hourly stock snapshots as Parquet files, enabling analysts to query historical availability trends. Each file contains a timestamp column in ISO 8601 UTC format, making filtering data by timestamp ranges the primary method for isolating specific windows to identify restocking patterns, regional bottlenecks, or flash sale impacts.

Understanding the Dataset Structure

The repository at nelsonjchen/bbl-tracker-public-db organizes data into deterministic URLs hosted on https://db-public.bbltracker.com/.

Component	Location	Purpose
manifest.json	https://db-public.bbltracker.com/manifest.json	JSON index mapping every Parquet filename to row counts, enabling programmatic discovery.
Parquet shards	https://db-public.bbltracker.com/YYYY-MM-DD-HHMM.parquet	Hourly snapshots containing columns: timestamp, product_name, variant_name, stock, region, is_flash_sale.
script.py	[script.py](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/script.py)	Reference implementation showing bottleneck detection queries on recent data.
reconstruct_db.py	[reconstruct_db.py](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/reconstruct_db.py)	Utility to merge a selectable time span into a local DuckDB file.

Method 1: Querying Timestamp Ranges with DuckDB SQL

DuckDB can query remote Parquet files directly without downloading them locally. The timestamp column stores strings in ISO 8601 format (e.g., 2026-02-16T18:00:00Z), allowing direct string comparison or casting to TIMESTAMP for truncation.

Basic Timestamp Filtering

Filter a specific window using the BETWEEN operator against ISO strings:

SELECT *
FROM read_parquet([
    'https://db-public.bbltracker.com/2026-02-16-0000.parquet',
    'https://db-public.bbltracker.com/2026-02-16-0600.parquet'
])
WHERE timestamp BETWEEN '2026-02-16T00:00:00Z' AND '2026-02-16T12:00:00Z';

Key details:

• read_parquet accepts a list of URLs; DuckDB streams them sequentially.
• String comparison works because ISO 8601 timestamps sort lexicographically.
• The WHERE clause ensures rows outside your desired window are excluded even if Parquet files overlap.

Aggregating Trends by Time Buckets

For trend analysis, aggregate metrics into hourly or daily buckets using date_trunc:

WITH hourly_stats AS (
    SELECT
        date_trunc('hour', timestamp::timestamp) AS hour,
        COUNT(*) AS total_snapshots,
        SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock_count
    FROM read_parquet($urls)  -- Parameter bound from Python
    WHERE region = 'us'
      AND timestamp BETWEEN $start AND $end
    GROUP BY 1
)
SELECT
    hour,
    total_snapshots,
    in_stock_count,
    ROUND(100.0 * in_stock_count / total_snapshots, 1) AS availability_pct
FROM hourly_stats
ORDER BY hour;

Performance notes:

• Casting timestamp::timestamp converts the ISO string to DuckDB’s native temporal type, enabling date_trunc.
• The GROUP BY 1 syntax references the first column in the SELECT list.
• This pattern appears in script.py lines 40-68, which computes availability percentages for bottleneck detection.

Method 2: Programmatic File Discovery with Python

When analyzing large date ranges, generating URLs manually is impractical. The repository provides two Pythonic approaches to select files based on timestamp ranges.

Using the Manifest for Dynamic Range Selection

The manifest.json index allows runtime discovery of valid files. The reconstruct_db.py script implements this workflow at lines 28-41:

import json
import urllib.request
from datetime import datetime, timezone

BASE_URL = "https://db-public.bbltracker.com"
MANIFEST_URL = f"{BASE_URL}/manifest.json"

# Fetch manifest

with urllib.request.urlopen(MANIFEST_URL) as resp:
    manifest = json.loads(resp.read())

def urls_for_range(start_iso: str, end_iso: str) -> list[str]:
    """Return parquet URLs for filenames falling between start and end dates."""
    start_date = start_iso[:10]  # Extract YYYY-MM-DD

    end_date = end_iso[:10]
    
    selected = [
        f"{BASE_URL}/{filename}"
        for filename in manifest["files"]
        if start_date <= filename[:10] <= end_date
    ]
    return sorted(selected)

# Example: Last 7 days

now = datetime.now(timezone.utc)
start = (now - timedelta(days=7)).strftime("%Y-%m-%dT%H:%M:%SZ")
end = now.strftime("%Y-%m-%dT%H:%M:%SZ")

urls = urls_for_range(start, end)
print(f"Selected {len(urls)} files for analysis")

Why this works:

• Filenames follow the pattern YYYY-MM-DD-HHMM.parquet, making lexical string comparison equivalent to chronological ordering.
• The manifest guarantees you only request existing files, preventing 404 errors.
• This approach is ideal for backtesting or historical analysis spanning weeks or months.

Deterministic URL Generation for Known Windows

For short, fixed ranges (e.g., a specific day), you can generate URLs directly without fetching the manifest:

from datetime import datetime, timedelta

BASE_URL = "https://db-public.bbltracker.com"

def generate_hourly_urls(date_str: str, hours: list[int]) -> list[str]:
    """Generate URLs for specific hours on a given date."""
    return [
        f"{BASE_URL}/{date_str}-{hour:02d}00.parquet"
        for hour in hours
    ]

# Example: Every 6 hours on February 16, 2026

urls = generate_hourly_urls("2026-02-16", [0, 6, 12, 18])

This pattern is documented in the README under "Option A: Specific Time Range (Recommended)" and is suitable for real-time dashboards or alerting systems that query recent snapshots.

Building a Local Database for Offline Analysis

For repeated analysis or large historical windows, the reconstruct_db.py script consolidates remote Parquet files into a single local DuckDB database.

Key features:

• Selects files based on a configurable time span (default: last 30 days).
• Downloads and ingests data into bambu_stock.duckdb.
• Enables offline querying without network latency.

Run the utility with:

uv run reconstruct_db.py

Then query the local file:

SELECT * FROM read_parquet('bambu_stock.duckdb')
WHERE timestamp > '2026-02-10T00:00:00Z';

This approach is optimal for Jupyter notebooks or business intelligence tools that require persistent, fast access to historical trends.

Summary

• Filter by timestamp ranges using DuckDB's BETWEEN operator against ISO 8601 strings in the timestamp column.
• Discover files programmatically via manifest.json to ensure you only fetch existing shards for your date window.
• Aggregate trends with date_trunc after casting timestamps to DuckDB's native temporal type.
• Use script.py for quick bottleneck detection on recent data, or reconstruct_db.py to build a local DuckDB database for offline historical analysis.
• Leverage lexical filename ordering (YYYY-MM-DD-HHMM.parquet) to generate URLs deterministically for short, fixed ranges without downloading the manifest.

Frequently Asked Questions

What is the timestamp format in the Parquet files?

The timestamp column stores values as ISO 8601 UTC strings (e.g., 2026-02-16T18:00:00Z). This format allows direct string comparison using SQL BETWEEN clauses because the lexicographical order matches chronological order. For temporal functions like date_trunc, cast the column to TIMESTAMP using timestamp::timestamp.

How do I filter data for the last 7 days only?

Fetch the manifest.json index and select filenames where the date prefix falls within the last 7 days. In Python, calculate the start date using datetime.now(timezone.utc) - timedelta(days=7), then compare the first 10 characters of each filename (the YYYY-MM-DD portion). Pass the resulting URLs to read_parquet and add a WHERE timestamp BETWEEN clause to exclude partial hours.

Can I analyze trends without downloading all the data?

Yes. DuckDB streams Parquet files remotely without saving them to disk. Use the read_parquet function with a list of HTTPS URLs to query only the shards overlapping your timestamp range. For repeated analysis, run reconstruct_db.py once to build a local bambu_stock.duckdb file, then query it offline without network overhead.

What is the difference between script.py and reconstruct_db.py?

script.py demonstrates a lightweight, stateless query against the most recent 4 Parquet shards (approximately 24 hours) to detect current bottlenecks. It uses Python f-strings to generate deterministic URLs and runs a single availability calculation. reconstruct_db.py is a data engineering utility that downloads a configurable time span (default: 30 days) from the manifest, merges all shards into a local DuckDB database, and enables complex historical analysis without repeated network requests.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}