tzdoctor 1.1.0

The most comprehensive SQL timezone migration auditor — catch timezone bugs before they reach production

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

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

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

Python 3.8+. Zero required dependencies.

---

Quickstart

# 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:

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

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

datetime.now()   # tzdoctor: ignore TZ005

---

Configuration file

Create .tzdoctor.yaml in your project root:

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:

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:

pip install "tzdoctor[postgres]"
pip install "tzdoctor[mysql]"

---

pytest integration

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

# 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:

[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:

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:

- 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

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

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