# Timezone Handling Guide
## 1. Best Practice: Store Time in UTC
**Keep all timestamps in UTC in the database.** This is the industry standard for servers.
| Component | Current Setting | Why It's Best |
|-----------|----------------|---------------|
| MySQL (Docker) | UTC (default) | No DST issues, universal, sync works across timezones |
| Go application | UTC (no `loc=` in DSN) | Consistent with MySQL, no conversion bugs |
| Source DB sync | UTC | `TIMESTAMP` stored as UTC internally, comparisons are correct |
**MySQL `TIMESTAMP` is always stored as UTC internally.** The display timezone only affects what you see, not what's stored.
---
## 2. Direct DB Query: Use `SET time_zone` Per Connection
When querying the database manually (MySQL CLI, Workbench, etc.):
```sql
-- Set timezone for this session
SET time_zone = 'America/Vancouver';
-- All TIMESTAMP columns now display in PST
SELECT * FROM sync_metadata;
-- Shows: 2026-07-16 11:31:37 (PST display)
-- Stored: 2026-07-16 18:31:37 (UTC — unchanged)
-- Query with PST times — MySQL converts automatically
SELECT * FROM sync_metadata WHERE last_run_at > '2026-07-16 11:00:00';
```
**Key points:**
- ✅ Display only — does NOT change stored UTC data
- ✅ Lasts for the entire session (all queries until you disconnect)
- ✅ Resets to UTC when connection closes
- ✅ Does NOT affect the Go application (separate connections)
- ⚠️ Only works for `TIMESTAMP` columns, not `DATETIME`
---
## 3. Why `SET time_zone` Can't Be Used in Go
Go uses a **connection pool** — connections are reused, not created/destroyed per query. If you `SET time_zone` on a connection, it **leaks** to the next function that gets the same connection.
### Connection Pool Diagram
```
┌─────────────────────────────────────────────────────────────────┐
│ Connection Pool (max 100 open, 50 idle, 300s lifetime) │
│ │
│ ┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐ │
│ │ conn1 │ │ conn2 │ │ conn3 │ │ ... │ ← 50 idle │
│ │ (UTC) │ │ (UTC) │ │ (UTC) │ │ (UTC) │ (pre-warmed) │
│ └────────┘ └────────┘ └────────┘ └────────┘ │
└─────────────────────────────────────────────────────────────────┘
Function A: SET time_zone on conn1
┌─────────────────────────────────────────────────────────────────┐
│ │
│ ┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐ │
│ │ conn1 │ │ conn2 │ │ conn3 │ │ ... │ │
│ │ (PST!) │ │ (UTC) │ │ (UTC) │ │ (UTC) │ │
│ └───┬────┘ └────────┘ └────────┘ └────────┘ │
│ │ │
│ └── Function A done, conn1 returned to pool │
│ conn1 STILL has PST! │
└─────────────────────────────────────────────────────────────────┘
Function B: gets conn1 from pool
┌─────────────────────────────────────────────────────────────────┐
│ │
│ ┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐ │
│ │ conn1 │ │ conn2 │ │ conn3 │ │ ... │ │
│ │ (PST!) │ │ (UTC) │ │ (UTC) │ │ (UTC) │ │
│ └───┬────┘ └────────┘ └────────┘ └────────┘ │
│ │ │
│ └── Function B gets conn1 — INHERITS PST! ← LEAKED! │
│ B's query runs in PST, not UTC │
└─────────────────────────────────────────────────────────────────┘
conn1 stays PST until:
- Connection recycled (after 300s lifetime)
- Explicitly reset with SET time_zone = 'UTC'
- Application restarts
```
### Pool Configuration (from `.env`)
```
DB_MAX_OPEN_CONNS=100 Max simultaneous connections
DB_MAX_IDLE_CONNS=50 Idle connections kept alive (pre-warmed)
DB_CONN_MAX_LIFETIME_SEC=300 Each connection recycled after 5 minutes
```
At rest: 50 idle connections waiting. Under load: up to 100 total. When load drops: extras closed back to 50.
### Why It Leaks
| What you think | What actually happens |
|---|---|
| Function ends → connection closed → timezone gone | Function ends → connection returned to pool → timezone persists |
| Next function gets fresh UTC connection | Next function might get the same PST connection |
| Each function is isolated | Timezone leaks for up to 300 seconds (connection lifetime) |
---
## 4. Solution: Convert Time in Go Code
### The Pattern
```
Input (PST) → Convert to UTC → Query DB (UTC) → Results (UTC) → Convert to PST → Display
```
### In Go Code
```go
// Load Vancouver timezone (handles PST/PDT automatically)
pstLoc, _ := time.LoadLocation("America/Vancouver")
// 1. Input: user wants logs after "2026-07-16 11:00:00" PST
pstTime, _ := time.ParseInLocation("2006-01-02 15:04:05", "2026-07-16 11:00:00", pstLoc)
// 2. Convert to UTC for query
utcTime := pstTime.UTC() // 2026-07-16 18:00:00 UTC
// 3. Query DB with UTC — no SET time_zone needed, no pool contamination
rows, err := db.Query(`
SELECT last_run_at, last_sync_time, last_run_status
FROM sync_metadata
WHERE last_run_at > ?
`, utcTime)
// 4. Read results (Go reads TIMESTAMP as UTC because no loc= in DSN)
for rows.Next() {
var lastRunAt time.Time // Go gives you UTC time
var lastSyncTime time.Time
var status string
rows.Scan(&lastRunAt, &lastSyncTime, &status)
// 5. Convert to PST for display
pstRunAt := lastRunAt.In(pstLoc) // 2026-07-16 11:31:37 PST
fmt.Printf("Run at: %s, Status: %s\n",
pstRunAt.Format("2006-01-02 15:04:05"), status)
}
```
### Why This Is Cost-Free
`time.Time.In(loc)` doesn't do any calculation — it just attaches the timezone location to the existing time value. The actual time moment doesn't change, only how it's displayed.
| Operation | Time per row | Comparison |
|---|---|---|
| `rows.Scan()` (parse from MySQL) | ~1-2 microseconds | 1,000x slower than conversion |
| Network I/O (fetching the row) | ~50-100 microseconds | 50,000x slower than conversion |
| `time.In(pstLoc)` (timezone conversion) | ~1-2 nanoseconds | Baseline — essentially free |
Even with 10,000 rows:
- Go conversion: 10,000 × 1ns = **0.01 milliseconds**
- MySQL fetching: 10,000 × 100μs = **1 second**
The timezone conversion is **0.001%** of the total time. Negligible.
### Why This Is Better Than Alternatives
| Approach | Safe? | MySQL Load | Pool Contamination | Complexity |
|---|---|---|---|---|
| `SET time_zone` in Go | ❌ No — leaks to pool | None | Yes | Low |
| `CONVERT_TZ()` in SQL | ✅ Safe | Adds CPU load | No | Medium |
| Convert in Go code | ✅ Safe | None | No | Low |
---
## 5. If You Ever Want to Change to PST (All or Nothing)
If you decide to move away from UTC, **all three changes must be made together**:
### Change 1: docker-compose.yml — both containers
```yaml
certgen:
environment:
- TZ=America/Vancouver # Go's time.Now() returns PST
mysql:
environment:
- TZ=America/Vancouver # MySQL's CURRENT_TIMESTAMP returns PST
```
### Change 2: db.go — local DB DSN (add `loc=Local`)
```go
dsn := "%s:%s@tcp(%s:%d)/%s?parseTime=true&loc=Local&multiStatements=true&timeout=10s"
```
### Change 3: db.go — source DB DSN (add `loc=Local`)
```go
dsn := "%s:%s@tcp(%s:%d)/%s?parseTime=true&loc=Local&timeout=10s"
```
### Why All Three
| If you change... | But not... | What breaks |
|---|---|---|
| `TZ` on both containers | DSN `loc=Local` | Go reads TIMESTAMP as UTC — mismatch with MySQL PST |
| DSN `loc=Local` | `TZ` on containers | Go uses container's UTC — `loc=Local` = UTC, no effect |
| MySQL `TZ` only | Go `TZ` + DSN | `CURRENT_TIMESTAMP` = PST, `time.Now()` = UTC — mismatch |
| Go `TZ` only | MySQL `TZ` + DSN | `time.Now()` = PST, `CURRENT_TIMESTAMP` = UTC — mismatch |
**All three or none. Partial changes cause mismatches.**
### Risks of Changing to PST
- ❌ Daylight saving time: March/November clock changes cause gaps or duplicates in sync
- ❌ Source DB must also be PST, or sync breaks
- ❌ If source DB is UTC, `modify_time` comparison is off by 7-8 hours
---
## 6. Quick Reference
| Scenario | What to do |
|---|---|
| **Keep current setup (recommended)** | Change nothing. Everything is UTC. |
| **View timestamps in PST manually** | `SET time_zone = 'America/Vancouver';` per MySQL session |
| **Query with PST time in Go** | Convert PST→UTC before query, convert UTC→PST after |
| **Change everything to PST** | Set `TZ` on both containers + `loc=Local` in both DSNs |
| **Check if a column auto-converts** | `TIMESTAMP` = yes, `DATETIME` = no |
