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

> Filter Bambu Lab filament stock data by timestamp ranges for trend analysis using DuckDB's BETWEEN clause or the manifest.json index. Analyze historical data effectively.

- Repository: [Nelson Chen/bbl-tracker-public-db](https://github.com/nelsonjchen/bbl-tracker-public-db)
- Tags: how-to-guide
- Published: 2026-03-08

---

**Use DuckDB's `read_parquet` function with `BETWEEN` clauses on ISO 8601 timestamp strings, or fetch the [`manifest.json`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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/main/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/main/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:

```sql
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`:

```sql
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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/manifest.json) index allows runtime discovery of valid files. The [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py) script implements this workflow at lines 28-41:

```python
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:

```python
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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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:

```bash
uv run reconstruct_db.py

```

Then query the local file:

```sql
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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py)** for quick bottleneck detection on recent data, or **[`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/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.