Timezone Handling

How HostMetrics handles timezones for trips, tolls, and display

Timezone Handling

HostMetrics has a two-tier timezone system because trip data and toll data come from different sources with different timezone behaviors.

The Two Systems

1. Trip Data (UTC Storage)

Aspect	Detail
Source	Turo CSV exports
Input timezone	Host’s local time (Turo exports in host’s market timezone)
Storage	timestamptz (UTC) in PostgreSQL
Display	Converted back to user’s configured timezone via formatDateTime()

When Turo CSV dates are inserted into PostgreSQL’s timestamptz columns, Postgres automatically converts them to UTC.

2. Toll Data (Local Storage)

Aspect	Detail
Source	Toll agency CSVs or scraped data
Input timezone	Agency’s operating timezone (e.g., NTTA = CST)
Storage	Plain date + text columns (NOT timestamptz)
Display	Shown as-is (no conversion)

Toll agencies report in their local timezone. Since the host is typically in the same timezone, the dates are already correct.

The Business Timezone Setting

In Settings > Data Sources, hosts configure their business timezone (e.g., America/Chicago).

This is NOT the user’s browser timezone — it’s “what timezone is my business in.”

Scenario	Behavior
Dallas host in Dallas	CST. Trips and tolls align.
Dallas host traveling to NYC	Still CST. Data stays consistent.
Dallas host with NY tolls	Tolls in EST, trips in CST. Dates still match (within 1 day).

Trip-to-Toll Date Matching

When matching tolls to trips (for reconciliation), both dates are compared as YYYY-MM-DD strings:

Trip date: trip_end → formatDateTime(trip_end, timezone) → "2026-01-15"
Toll date: transaction_date (already stored as "2026-01-15")
Match:     string comparison → equal

This works because both represent the “local date” in the host’s market.

Critical Rule: Date Parsing

When parsing toll CSV dates, NEVER use toISOString():

// WRONG — shifts to UTC, may change the date by +/- 1 day
const date = new Date("1/15/2026").toISOString().split("T")[0];
// Could become "2026-01-14" if parsed in a timezone behind UTC!

// CORRECT — preserves local date components
const parsed = new Date("1/15/2026");
const y = parsed.getFullYear();
const m = String(parsed.getMonth() + 1).padStart(2, "0");
const d = String(parsed.getDate()).padStart(2, "0");
const date = `${y}-${m}-${d}`; // Always "2026-01-15"

Display Formatting

// For trip timestamps (UTC → local):
import { formatDateTime } from "@/lib/formatters";
formatDateTime(trip.trip_end, "America/Chicago"); // "Jan 15, 2026 3:00 PM"

// For toll dates (already local, display as-is):
formatDate(toll.transaction_date); // "Jan 15, 2026"

// For date-only display (avoid timezone issues):
import { formatDate } from "@/lib/formatters";
const [year, month, day] = dateStr.split("T")[0].split("-").map(Number);
const date = new Date(year, month - 1, day);

Key Files

File	Purpose
src/lib/formatters.ts	formatDateTime() — UTC to local conversion
src/lib/db/toll-transactions.ts	Toll date parsing (local components)
src/app/api/tolls/sync/route.ts	Server-side toll date parsing