FeathersJS service and Knex adapter: regional timestamp conversion boundary

Direct answer

Place regional time-zone conversion in a FeathersJS service hook, not in Knex adapter configuration or the database session. Knex forwards Date objects and ISO strings to the driver without framework-wide normalization, and session settings vary by deployment. A hook is the only boundary where the service can control both inbound normalization and outbound formatting consistently for REST and Socket.io.

Confirmed behavior and assumptions

Assumptions: FeathersJS v5, a Knex-backed service, and a relational driver such as PostgreSQL, MySQL, or SQLite. Adapter versions differ in how they coerce Date objects, so treat driver-specific behavior as unverified until you inspect your own stack.

• FeathersJS core does not normalize time zones before calling the adapter.
• Knex passes values to the database driver; the driver and session decide whether input is read as server local time or UTC.
• JSON.stringify(new Date()) always emits a UTC ISO string, so real-time payloads carry UTC unless the service serializes dates differently.

Recommended placement

1. Inbound writes: Use a before hook to convert incoming Date or ISO values to UTC before Knex persists them. Prefer timestamptz in PostgreSQL or an explicit UTC column type in MySQL or SQLite.
2. Database session: Set the Knex connection session to UTC in adapter configuration as a guardrail, not as the primary conversion strategy. For PostgreSQL this can be SET timezone = 'UTC'; for MySQL, SET time_zone = '+00:00'. Run this only against a local test database first.
3. Outbound reads: Use an after hook or Feathers serializer to format timestamps for clients. If you need a regional string, produce it there; otherwise document that clients receive UTC ISO and convert client-side.
4. Queries: Normalize filter datetime values to UTC in a before hook before Knex builds the query. Non-UTC offsets are driver-dependent and are not standardized by FeathersJS.

Transport consistency

REST and Socket.io invoke the same service methods, so query semantics do not differ by transport. Any observed difference is likely caused by client-side date parsing or response middleware, not by FeathersJS or Knex. A service cannot guarantee regional formatting without client-side conversion unless it explicitly serializes dates to regional strings in every response path, including real-time events.

Verification

1. Insert a test record through the service and inspect the generated Knex SQL to confirm the datetime literal matches your expected UTC format.
2. Read the same record via REST and Socket.io and compare the serialized timestamp fields byte-for-byte.
3. Run knex.raw('SHOW timezone') on PostgreSQL or knex.raw('SELECT @@session.time_zone') on MySQL against a local test database to confirm the session is UTC.

Missing diagnostic detail: which database driver and Knex adapter version are you using? This changes whether timestamptz, DATETIME, or TEXT storage applies, and therefore changes the exact hook implementation.