Sequelize v6 DATE type timezone handling shift: impact on storage and retrieval

Direct answer

No — Sequelize should not apply the instance timezone to DATE values for PostgreSQL timestamptz columns. The current v6 behavior (delegate conversion to the driver’s session timezone) is the correct default because it avoids double‑shifting and preserves backward compatibility. For MySQL DATETIME and SQLite TEXT columns, where the driver does not perform timezone conversion, an opt‑in flag would let applications request Sequelize‑level conversion without breaking existing PostgreSQL workflows.

Why the current behavior is correct for PostgreSQL

• Driver‑level conversion: The pg driver executes SET TIME ZONE using the instance timezone option. When a timestamptz value is read, PostgreSQL returns it already shifted to that session zone. The JavaScript Date object Sequelize constructs therefore holds the correct UTC instant — no further conversion is needed.
• Double‑shift risk: If Sequelize applied its own offset on top of the driver’s conversion, every read/write cycle would move the timestamp by the instance offset again, silently corrupting data for any application that upgrades or changes its timezone setting.
• Backward compatibility: Existing v6 applications rely on this “trust the driver” contract. Changing the default would be a breaking change with no safe automatic migration.

Dialect‑specific reality

Dialect	Column type for DATE	Who converts	Current Sequelize behavior
PostgreSQL	timestamptz	Driver (session timezone)	Passes timezone to pool; no extra conversion
MySQL	DATETIME (naive)	Nobody	Writes local components per timezone; reads back as naive local
MySQL	TIMESTAMP (deprecated)	Driver (session timezone)	Same as PostgreSQL — trust driver
SQLite	TEXT (ISO8601)	Nobody	Writes toISOString() (UTC) or local offset string per timezone

Migration strategy if you change timezone after sync()

1. PostgreSQL timestamptz: Run a one‑time script that reads each row as UTC (SELECT col AT TIME ZONE 'UTC' ...) and writes it back under the new session timezone. The stored UTC instant stays identical.
2. MySQL DATETIME: Update each row with CONVERT_TZ(column, oldOffset, newOffset) so the naive wall‑clock time reflects the new zone.
3. SQLite: Rewrite the ISO strings in each row to the new offset (e.g., 2024-06-15T12:00:00+00:00 → 2024-06-15T14:00:00+02:00).

These scripts must run while the application is quiesced or within a transaction to avoid races.

Configuration flag proposal (not in v6)

A future flag dateTimezoneConversion: 'driver' | 'sequelize' | 'none' would let users choose per dialect:

• 'driver' (default for PostgreSQL/MySQL TIMESTAMP) — current behavior.
• 'sequelize' — Sequelize applies the instance offset itself; useful for MySQL DATETIME and SQLite where the driver does nothing.
• 'none' — raw Date objects passed through untouched; pair with dateStrings: true for full control.

Until such a flag lands, the workaround for MySQL DATETIME/SQLite is to set timezone: '+00:00' and handle conversion in application code, or use dateStrings: true and parse manually.

Verification steps (run before relying on any behavior)

1. PostgreSQL: SELECT now() AT TIME ZONE 'UTC' AS utc_now, now() AS local_now; — confirm local_now matches the instance timezone.
2. Insert a known UTC date (new Date('2024-06-15T12:00:00Z')), then raw-query SELECT col, col AT TIME ZONE 'UTC' FROM table; — verify stored UTC instant.
3. Repeat with timezone: '+02:00' and timezone: '-05:00'; the returned Date.getTime() must be identical across all cases if driver‑level conversion is working correctly.
4. For MySQL, check SELECT @@session.time_zone; after connection.
5. For SQLite, inspect the raw TEXT value stored in the column to confirm it matches the expected ISO string for the configured timezone.

One missing diagnostic detail

Which dialect(s) are you targeting in production? The migration strategy and flag priority differ significantly between PostgreSQL‑only, MySQL‑only, and multi‑dialect deployments.