formatting-datetimes

name	formatting-datetimes
description	Format dates and timestamps in SQL queries instead of JS Date instances. Use when rendering dates, date/times, timestamps, or times from the database, when user mentions date formatting, toLocaleDateString, toLocaleString, or timezone issues, or when working with SSR hydration mismatches involving dates.

Formatting Datetimes

Format all date/time values from the database in SQL using PostgreSQL's to_char() and return pre-formatted strings. Never rely on JS Date instances for display.

Why

1. SSR hydration mismatch: toLocaleDateString() and toLocaleString() produce different output on the server (Node.js locale/timezone) vs the client (browser locale/timezone), causing React hydration errors.
2. Timezone ambiguity: The browser's locale and timezone determine the output, making it unpredictable. Formatting in SQL with the database's session timezone (UTC) as the source of truth eliminates ambiguity.

Database timezone

The database session timezone is set to UTC via SET timezone TO 'UTC' on every connection (app/framework/db.server.ts).

Format patterns

Date only

For values displayed without time, use 'YYYY-MM-DD':

sql<string>`to_char(created_at, 'YYYY-MM-DD')`.as('createdAt')

Output: 2026-02-22

Datetime in UTC (admin/debug)

For admin/debug values rendered in UTC (job runAt, audit trails), always include the TZ format specifier so the timezone appears in the output:

sql<string | null>`to_char(run_at, 'YYYY-MM-DD HH24:MI TZ')`.as('runAt')

Output: 2026-02-22 14:30 UTC

Company-local display

User-facing labels on company-local surfaces (receiving, production runs, staff scheduling) are formatted at the company's configured time zone, WITHOUT the TZ suffix — the company time zone is implied:

import { dateInCompanyTimezone, todayInCompanyTimezone } from '~/business/company-timezones.common'

sql<string>`to_char(scheduled_at at time zone ${sql.lit(currentCompany.timezone)}, 'HH24:MI')`.as('scheduledTime')

Output: 14:30

The time zone is per-company configuration, stored append-only in companyTimezoneRevisions (default America/Los_Angeles). app/business/company-timezones.common.ts is the single home of the helpers (dateInCompanyTimezone(timezone, instant), todayInCompanyTimezone(timezone), DEFAULT_COMPANY_TIMEZONE, companyTimezoneSchema). They take the time zone string as their first argument — never hardcode a zone.

• Context-bearing business functions read currentCompany.timezone (populated on the auth context) and pass it into the helpers.
• Seed and job paths without the auth context call currentCompanyTimezone(executor, companyId) from ~/business/companies.server to resolve the string first.

The time zone is user-configurable and interpolated via sql.lit, so every write path validates it against companyTimezoneSchema — a strict whitelist backed by Intl.supportedValuesOf('timeZone'). Routes carry no time zone knowledge beyond threading currentCompany.timezone.

timestamp vs timestamptz columns

• timestamptz columns: to_char(..., '... TZ') works directly — the timezone abbreviation comes from the session timezone.
• timestamp (without timezone) columns: The TZ specifier won't produce output. If timezone is needed, cast first: to_char(created_at::timestamptz, '... TZ'). Since the session timezone is UTC, the cast interprets the naive timestamp as UTC.

For date-only formatting, no cast is needed regardless of column type.

Never wrap a plain date column in AT TIME ZONE

A date column has no time-of-day to convert. Wrapping it in AT TIME ZONE makes Postgres cast it to a timestamp at UTC midnight first and then shift it — landing on the previous calendar day for any zone west of UTC, in both DST arms. The law:

• timestamptz columns: at time zone <zone> first, then cast ::date if a local calendar day is needed.
• date columns: use as-is. No AT TIME ZONE, ever.

Every derivation with a day boundary (expiration, lot numbering, "today" comparisons, week windows) gets a test asserting the exact calendar day on both sides of a zone-adjacent boundary — two UTC instants that land on different local dates. Bucket assertions (±N days) cannot catch a one-day shift.

Component rendering

Since values arrive as pre-formatted strings, render them directly:

Instead of:

{new Date(record.createdAt).toLocaleDateString()}

Do:

{record.createdAt}

Move Date comparisons to SQL

When business logic compares timestamps (e.g., "is this date in the future?"), move that comparison to SQL instead of doing it in JS with new Date(). Use Kysely's case() builder or computed columns.

Instead of:

const jobs = rawJobs.map((job) => ({
  ...job,
  isScheduled: job.runAt && new Date(job.runAt) > new Date(),
}))

Do:

.select((eb) =>
  eb
    .case()
    .when(eb.and([
      eb('lockedAt', 'is', null),
      eb('runAt', '>', sql<Date>`now()`),
    ]))
    .then(true)
    .else(false)
    .end()
    .as('isScheduled'),
)

This keeps all temporal logic on the database side where the timezone is controlled and now() is authoritative.

CamelCasePlugin reminder

Column names inside sql template literals must use snake_case (raw SQL bypasses the plugin). The .as() alias uses camelCase (goes through the plugin):

sql<string>`to_char(created_at, 'YYYY-MM-DD')`.as('createdAt')
//                  ^^^^^^^^^^                      ^^^^^^^^^
//                  snake_case (raw SQL)            camelCase (builder)