51 lines
1.1 KiB
Python
51 lines
1.1 KiB
Python
"""capture uptime
|
|
|
|
Revision ID: d8f4a1c6e2b7
|
|
Revises: d7a3c5e9f2b1
|
|
Create Date: 2026-10-19 03:00:00.000000
|
|
|
|
"""
|
|
|
|
from collections.abc import Sequence
|
|
|
|
from alembic import op
|
|
|
|
revision: str = "d8f4a1c6e2b7"
|
|
down_revision: str | None = "d7a3c5e9f2b1"
|
|
branch_labels: str | Sequence[str] | None = None
|
|
depends_on: str | Sequence[str] | None = None
|
|
|
|
_UPTIME = """
|
|
CREATE TABLE capture_uptime (
|
|
account_id integer NOT NULL,
|
|
started_at timestamptz NOT NULL,
|
|
seen_at timestamptz NOT NULL,
|
|
PRIMARY KEY (account_id, started_at)
|
|
)
|
|
"""
|
|
|
|
_BACKFILL = """
|
|
INSERT INTO capture_uptime (account_id, started_at, seen_at)
|
|
SELECT account_id, min(ts), max(ts)
|
|
FROM (
|
|
SELECT account_id, ts,
|
|
count(*) FILTER (WHERE gap > interval '30 minutes')
|
|
OVER (PARTITION BY account_id ORDER BY ts) AS stretch
|
|
FROM (
|
|
SELECT account_id, ts,
|
|
ts - lag(ts) OVER (PARTITION BY account_id ORDER BY ts) AS gap
|
|
FROM presence
|
|
) gaps
|
|
) stretches
|
|
GROUP BY account_id, stretch
|
|
"""
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.execute(_UPTIME)
|
|
op.execute(_BACKFILL)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.execute("DROP TABLE IF EXISTS capture_uptime")
|