Metadata-Version: 2.4
Name: tzdoctor
Version: 1.1.0
Summary: The most comprehensive SQL timezone migration auditor — catch timezone bugs before they reach production
License-Expression: MIT
Project-URL: Homepage, https://github.com/cortexresearch/tzdoctor
Project-URL: Documentation, https://github.com/cortexresearch/tzdoctor#readme
Project-URL: Bug Tracker, https://github.com/cortexresearch/tzdoctor/issues
Project-URL: Changelog, https://github.com/cortexresearch/tzdoctor/releases
Keywords: sql,timezone,migration,audit,linter,postgresql,mysql,snowflake,bigquery,redshift,clickhouse,spark,datetime,utc
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: System Administrators
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.8
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Quality Assurance
Classifier: Topic :: Software Development :: Testing
Classifier: Framework :: Pytest
Requires-Python: >=3.8
Description-Content-Type: text/markdown
License-File: LICENSE
Provides-Extra: postgres
Requires-Dist: psycopg2-binary>=2.9; extra == "postgres"
Provides-Extra: mysql
Requires-Dist: pymysql>=1.0; extra == "mysql"
Provides-Extra: config
Requires-Dist: PyYAML>=6.0; extra == "config"
Provides-Extra: all
Requires-Dist: psycopg2-binary>=2.9; extra == "all"
Requires-Dist: pymysql>=1.0; extra == "all"
Requires-Dist: PyYAML>=6.0; extra == "all"
Dynamic: license-file
Dynamic: requires-python

# TZ Doctor

**The most comprehensive SQL timezone migration auditor.**

TZ Doctor scans your SQL files, ORM models, migration scripts, and job configs to find every place that assumes the server timezone — across MySQL, PostgreSQL, Snowflake, BigQuery, Redshift, ClickHouse, Spark SQL, SQLite, SQL Server, and Oracle. It tells you exactly what will break, why, and how to fix it — before you move a database to a new region, migrate to UTC, or switch cloud providers.

---

## Installation

```bash
pip install tzdoctor

# With database probe support
pip install "tzdoctor[postgres]"   # psycopg2-binary
pip install "tzdoctor[mysql]"      # pymysql
pip install "tzdoctor[config]"     # PyYAML for .tzdoctor.yaml
pip install "tzdoctor[all]"        # everything
```

```bash
# uv
uv add tzdoctor
uv tool install tzdoctor    # global CLI
uvx tzdoctor ./sql          # run without installing
```

**Python 3.8+. Zero required dependencies.**

---

## Quickstart

```bash
# Scan a directory
tzdoctor ./sql

# Scan specific files
tzdoctor billing.sql schema.sql models.py

# Full fix examples inline
tzdoctor . --verbose

# CI gate — exit 1 if any HIGH finding
tzdoctor . --fail-on HIGH

# Probe a live database for runtime TZ issues
tzdoctor . --connect postgresql://user:pass@host/db

# SARIF output for GitHub Security tab
tzdoctor . --format sarif -o results.sarif

# Shareable HTML report
tzdoctor . --format html -o report.html

# Rule filtering
tzdoctor . --rules TZ001,TZ002          # only run these rules
tzdoctor . --skip-rules TZ004           # skip cron checks
```

---

## Example output

```
╔══════════════════════════════════════════════════════════╗
║         TZ Doctor  —  SQL Timezone Migration Auditor     ║
╚══════════════════════════════════════════════════════════╝

  Files scanned : 4    Findings : 41  (22H  13M  5L  1I)

  SAFETY SCORECARD
  ────────────────────────────────────────────────────────────
  Schema Safety    ███░░░░░░░░░░░░░░░░░   16/100  F  7H  2M  2L
  Query Safety     ░░░░░░░░░░░░░░░░░░░░    0/100  F  8H  5M
  Parse Safety     ████████░░░░░░░░░░░░   42/100  D  2H  3M
  ORM Safety       ████████████░░░░░░░░   60/100  D  4H
  Job Safety       █████████████░░░░░░░   64/100  D  1H  4M  3L
  ────────────────────────────────────────────────────────────
  Overall          █████░░░░░░░░░░░░░░░   26/100

  Migration Verdict  ✗  NOT READY
  22 HIGH findings will break after migration. Fix these first.
```

Each finding includes the file, line number, plain-English explanation, and a corrected example for the right dialect.

---

## What it detects

### TZ001 — Dangerous Time Functions

Functions that return current time in server or session timezone.

| Function | Dialects | Risk |
|---|---|---|
| `NOW()` | PostgreSQL · MySQL · Redshift | HIGH |
| `CURRENT_DATE` | All | HIGH |
| `GETDATE()` | SQL Server | HIGH |
| `SYSDATE()` | MySQL · Oracle | HIGH |
| `LOCALTIME` / `LOCALTIMESTAMP` | PostgreSQL | HIGH |
| `CURDATE()` / `CURTIME()` | MySQL | HIGH |
| `datetime('now', 'localtime')` | SQLite | HIGH |
| `today()` / `yesterday()` | ClickHouse | HIGH |
| `CURRENT_TIMESTAMP` | All | MEDIUM |
| `CURRENT_TIME` | All | MEDIUM |

---

### TZ002 — Timezone-Naive Column Types

Schema declarations that store no timezone information.

| Pattern | Dialects | Risk |
|---|---|---|
| `col DATETIME` | MySQL · SQLite · ClickHouse | HIGH |
| `col DateTime` (without `'UTC'` arg) | ClickHouse | HIGH |
| `col TIMESTAMP_NTZ` | Snowflake | HIGH |
| `col VARCHAR` for `*_at` / `*_time` columns | All | HIGH |
| `col TIMESTAMP WITHOUT TIME ZONE` | PostgreSQL | HIGH |
| `col TIMESTAMP` (ambiguous, time-named column) | PostgreSQL | MEDIUM |
| `col TIMESTAMP_LTZ` | Snowflake | INFO |
| `col DATE` (time-named column) | All | LOW |

---

### TZ003 — Timezone-Unsafe Date Comparisons

Query patterns that produce different results depending on session timezone.

| Pattern | Risk |
|---|---|
| `WHERE dt = CURRENT_DATE` | HIGH |
| `DATE(NOW())` / `DATE(CURRENT_TIMESTAMP())` | HIGH |
| `INTERVAL '1 day'` / `INTERVAL '24 HOURS'` (DST risk) | MEDIUM |
| `date_trunc('day', col)` without `AT TIME ZONE` | MEDIUM |
| `BETWEEN ... AND NOW()` | MEDIUM |
| `EXTRACT(EPOCH FROM col)` without `AT TIME ZONE` | LOW |

---

### TZ004 — Scheduled Job Timezone Risk

Cron and scheduler configurations with no pinned timezone.

| Pattern | Tool | Risk |
|---|---|---|
| `0 0 * * *` with no `CRON_TZ=` | cron | HIGH |
| `start_date = datetime(2024, 1, 1)` (naive) | Airflow | HIGH |
| `0 <hour> * * *` with no `TZ=` | cron | MEDIUM |
| `{{ current_timestamp() }}` in dbt models | dbt | MEDIUM |
| `CronSchedule(...)` without `timezone=` | Prefect | MEDIUM |
| `crontab()` without `CELERY_TIMEZONE` | Celery | MEDIUM |
| `cron.schedule()` without `timezone` option | Node.js | MEDIUM |
| `IntervalTrigger()` without `timezone=` | APScheduler | MEDIUM |
| `schedule.every().day.at(...)` | Python schedule | MEDIUM |

GitHub Actions cron is always UTC — flagged as INFO only.

---

### TZ005 — ORM / Storage Timezone Mismatch

Application-layer patterns that write timezone-naive values.

| Pattern | Tool | Risk |
|---|---|---|
| `Column(DateTime())` without `timezone=True` | SQLAlchemy | HIGH |
| `sa.DateTime()` in Alembic migrations | Alembic | HIGH |
| `models.DateTimeField()` without `USE_TZ=True` | Django | HIGH |
| `datetime.utcnow()` (deprecated, returns naive) | Python | HIGH |
| `datetime.now()` without `timezone.utc` | Python | HIGH |
| `@Temporal(TemporalType.TIMESTAMP)` | Java/JPA | HIGH |
| `INSERT INTO t VALUES ('2024-01-15 10:30:00')` | SQL | MEDIUM |
| `DataTypes.DATE` / `DataTypes.DATETIME` | Sequelize | MEDIUM |
| `@Column('datetime')` / `@Column('timestamp')` | TypeORM | MEDIUM |
| `DatetimeField()` (naive default) | Tortoise ORM | MEDIUM |
| `DateTimeField()` without UTC enforcement | Peewee | MEDIUM |
| `CONVERT_TZ()` (implies non-UTC storage) | MySQL | LOW |
| `time.Time` without `loc=UTC` DSN | Go/GORM | LOW |

---

### TZ006 — Dialect Date Parse Functions

Functions that extract date parts using session or server timezone.

| Function | Dialects | Risk |
|---|---|---|
| `DATE_FORMAT(ts, fmt)` | MySQL · MariaDB | HIGH |
| `formatDateTime(ts, fmt)` | ClickHouse | HIGH |
| `from_unixtime(ts)` | Spark SQL | HIGH |
| `unix_timestamp(str)` | Spark SQL | HIGH |
| `DATE(ts)` | MySQL · BigQuery | MEDIUM |
| `WEEK(ts)` / `WEEKDAY(ts)` / `DAYOFWEEK(ts)` | MySQL | MEDIUM |
| `to_char(ts, fmt)` | PostgreSQL | MEDIUM |
| `EXTRACT(DOW/HOUR/... FROM ts)` | PostgreSQL | MEDIUM |
| `date_part(field, ts)` | PostgreSQL | MEDIUM |
| `toDate(ts)` / `toStartOfDay/Hour/Week(ts)` | ClickHouse | MEDIUM |
| `toDayOfWeek(ts)` / `toHour(ts)` | ClickHouse | MEDIUM |
| `DATETIME(ts)` without timezone arg | BigQuery | MEDIUM |
| `TIMESTAMP_TRUNC(ts, part)` | BigQuery | MEDIUM |
| `to_utc_timestamp(ts, tz)` / `from_utc_timestamp(ts, tz)` | Spark SQL | MEDIUM |
| `DATEADD(day, n, ts)` / `DATEDIFF(day, ts1, ts2)` | Redshift · SQL Server | MEDIUM/LOW |
| `DAYNAME(ts)` / `TIME_FORMAT(ts, fmt)` | MySQL | MEDIUM |

---

### TZ007 — Snowflake Timezone Pitfalls

Snowflake-specific patterns that are not obvious from general SQL knowledge.

| Pattern | Risk |
|---|---|
| `CONVERT_TIMEZONE('target', ts)` — 2-arg uses session TZ as source | HIGH |
| `TO_TIMESTAMP_NTZ(ts)` — explicit no-timezone storage | HIGH |
| `TRY_TO_TIMESTAMP_NTZ(ts)` | HIGH |
| `TO_TIMESTAMP_LTZ(ts)` — stores UTC but extractions are session-TZ-dependent | MEDIUM |
| `TIMESTAMPADD(day, n, ts)` on `TIMESTAMP_NTZ` — DST-unsafe | MEDIUM |
| `DATEADD(day, n, ts)` on `TIMESTAMP_NTZ` | MEDIUM |
| `TIMESTAMP_FROM_PARTS(...)` — returns `TIMESTAMP_NTZ` by default | MEDIUM |

**The 2-arg `CONVERT_TIMEZONE` trap:**
```sql
-- RISKY: source timezone is @@session.TIMEZONE, not UTC
CONVERT_TIMEZONE('America/New_York', created_at)

-- SAFE: always explicit 3-arg
CONVERT_TIMEZONE('UTC', 'America/New_York', created_at)
```

---

## Inline suppression

Suppress a finding on a specific line:

```sql
SELECT NOW();                       -- fires TZ001
SELECT NOW();  -- tzdoctor: ignore  -- suppressed (all rules)
SELECT NOW();  -- tzdoctor: ignore TZ001  -- suppressed (TZ001 only)
```

```python
datetime.now()   # tzdoctor: ignore TZ005
```

---

## Configuration file

Create `.tzdoctor.yaml` in your project root:

```yaml
fail_on: HIGH
min_risk: LOW

exclude:
  - "migrations/legacy/**"
  - "test_fixtures/*.sql"

skip_rules:
  - TZ004          # handled by separate infra team

connect: postgresql://readonly@prod-db/mydb
```

The config is auto-discovered by walking up from the first scanned path. Supports `.tzdoctor.yaml`, `.tzdoctor.yml`, `tzdoctor.yaml`, `tzdoctor.yml`.

---

## Live database probe

Connect to a real database to catch what static analysis can't:

```bash
tzdoctor . --connect postgresql://user:pass@host/db
tzdoctor . --connect mysql://user:pass@host/db
```

The probe checks:
- Session timezone (`TimeZone` GUC / `@@session.time_zone`)
- Global timezone vs session timezone mismatch
- Whether `NOW()` differs from UTC equivalent
- `SYSTEM` timezone inheritance (MySQL) — the silent killer

```
  LIVE DATABASE PROBE
  ────────────────────────────────────────────────────────────
  Connected   : MySQL 8.0.32  (mysql://app@prod-db/mydb)
  Global TZ   : UTC
  Session TZ  : America/Los_Angeles  ⚠ NOT UTC
  NOW()       : 2024-01-15 10:30:00
  UTC NOW     : 2024-01-15 18:30:00

  ⚠  2 runtime issues found:
  ⚠  @@session.time_zone is 'America/Los_Angeles', not UTC.
     NOW(), DATE(), DATE_FORMAT(), WEEK(), and all date extract
     functions return local-time results.
```

Install the driver for your database:
```bash
pip install "tzdoctor[postgres]"
pip install "tzdoctor[mysql]"
```

---

## pytest integration

The pytest plugin is auto-registered after `pip install tzdoctor`. No `conftest.py` needed.

```bash
# Run TZ Doctor as part of your test suite
pytest --tz-scan ./sql --tz-scan ./models --tz-fail-on HIGH

# Include live database probe
pytest --tz-scan . --tz-connect postgresql://user:pass@host/db
```

**Configure in `pyproject.toml` or `pytest.ini`** so you never have to pass flags in CI:

```toml
[tool.pytest.ini_options]
tz_scan    = ["sql", "models", "migrations"]
tz_fail_on = "HIGH"
tz_connect = "postgresql://readonly@prod-db/mydb"
```

Each scanned file becomes one test item — files with findings at the threshold fail, files without findings pass:

```
FAILED ::tzdoctor::sql/billing.sql
  TZ Doctor: 3 timezone issue(s) in sql/billing.sql
  [HIGH] line 42 — TZ001 Dangerous Time Function
    WHERE invoice_date >= NOW() - INTERVAL '30 days'
    Problem : NOW() returns session timezone, not UTC.
    Fix     : Use NOW() AT TIME ZONE 'UTC'

  TZ Doctor summary
  Scanned 12 file(s)  —  10 passed  2 failed  0 error(s)
```

---

## Pre-commit hook

Add to `.pre-commit-config.yaml`:

```yaml
repos:
  - repo: https://github.com/cortexresearch/tzdoctor
    rev: v1.1.0
    hooks:
      - id: tzdoctor           # fails on HIGH (default)
      - id: tzdoctor-strict    # fails on MEDIUM+
```

---

## GitHub Actions — SARIF upload

SARIF output integrates with the GitHub Security tab:

```yaml
- name: TZ Doctor audit
  run: |
    pip install tzdoctor
    tzdoctor . --format sarif -o tz-results.sarif

- name: Upload to GitHub Security
  uses: github/codeql-action/upload-sarif@v3
  with:
    sarif_file: tz-results.sarif
    category: tzdoctor
```

Findings appear as code scanning alerts on PRs, with file, line, and fix description inline.

---

## Output formats

| Format | Use case |
|---|---|
| `console` (default) | Human-readable terminal output with ANSI color |
| `json` | CI pipelines, dashboards, Slack bots, downstream tooling |
| `html` | Self-contained shareable audit report (~60 KB) |
| `sarif` | GitHub Security tab, VS Code, any SARIF-compatible tool |

```bash
tzdoctor . --verbose                            # full fix examples in terminal
tzdoctor . --format json | jq '.summary'       # CI-friendly summary
tzdoctor . --format html -o report.html        # open in browser
tzdoctor . --format sarif -o results.sarif     # GitHub Security
```

---

## Scorecard dimensions

| Dimension | Rules | Weight |
|---|---|---|
| **Schema Safety** | TZ002 | 2× |
| **Query Safety** | TZ001, TZ003 | 2× |
| **Parse Safety** | TZ006, TZ007 | 2× |
| **ORM Safety** | TZ005 | 1× |
| **Job Safety** | TZ004 | 1× |

**Migration verdicts:**

| Verdict | Meaning |
|---|---|
| `✓ READY` | No HIGH findings. Run a final spot-check, then migrate. |
| `⚠ REVIEW REQUIRED` | No HIGH findings but MEDIUM issues remain. |
| `⚠ NEEDS WORK` | Resolve the HIGH findings before scheduling. |
| `✗ NOT READY` | HIGH findings will break immediately after migration. |

---

## Project layout

```
tzaudit/
├── rules/
│   ├── dangerous_functions.py   # TZ001 — NOW(), GETDATE(), today(), …
│   ├── column_types.py          # TZ002 — DATETIME, TIMESTAMP_NTZ, DateTime…
│   ├── date_comparisons.py      # TZ003 — DATE(NOW()), date_trunc, …
│   ├── cron_risks.py            # TZ004 — cron, Airflow, Celery, dbt, Prefect
│   ├── storage_mismatch.py      # TZ005 — SQLAlchemy, Django, Alembic, Peewee, …
│   ├── dialect_functions.py     # TZ006 — DATE_FORMAT, to_char, Spark, ClickHouse, …
│   └── snowflake.py             # TZ007 — CONVERT_TIMEZONE, TIMESTAMP_NTZ, …
├── scanner.py                   # File walking, rule dispatch, inline suppression
├── reporter.py                  # Console, JSON, HTML, SARIF output
├── probe.py                     # Live database probe (PostgreSQL + MySQL)
├── config.py                    # .tzdoctor.yaml loading
├── models.py                    # Finding, ScanReport, scoring
├── cli.py                       # CLI entry point
└── pytest_plugin.py             # pytest11 plugin
tests/                           # 198 tests
examples/                        # Intentionally buggy SQL/Python for demos
.pre-commit-hooks.yaml           # Pre-commit integration
```

---

## Supported dialects

| Dialect | Rules |
|---|---|
| PostgreSQL | TZ001 TZ002 TZ003 TZ005 TZ006 |
| MySQL · MariaDB | TZ001 TZ002 TZ003 TZ005 TZ006 |
| Snowflake | TZ002 TZ006 TZ007 |
| BigQuery | TZ006 |
| Redshift | TZ001 TZ006 |
| ClickHouse | TZ001 TZ002 TZ006 |
| Spark SQL | TZ006 |
| SQL Server | TZ001 TZ006 |
| Oracle | TZ001 |
| SQLite | TZ001 TZ002 |

---

## Running the tests

```bash
pip install pytest
pytest tests/ -v
```

198 tests covering true positives, safe-pattern negatives, comment stripping, case insensitivity, directory walking, scoring, all output formats, and XSS escaping in the HTML reporter.

---

## License

MIT
