E0033 timestamp-timezone-mismatch

Comparing or assigning timestamp (without time zone) with timestamptz (with time zone) depends on the session's TimeZone setting.

What it reports

Column-to-column comparisons, joins and assignments between timestamp and timestamp with time zone (or timestamptz) in PostgreSQL and Redshift.

PostgreSQL converts between timestamp and timestamptz using the active session's TimeZone. As a result, the comparison or assignment depends on the client connection configuration:

• In a local development environment where TimeZone = 'UTC', comparisons may align as expected.
• In production where TimeZone = 'Asia/Tokyo' or another offset, the values shift by the session offset, yielding different query rows or corrupting timestamp data on assignment.

Example

Given the schema:

CREATE TABLE events (
  id INT PRIMARY KEY,
  occurred_at TIMESTAMPTZ,
  local_at TIMESTAMP
);

CREATE TABLE schedules (
  id INT PRIMARY KEY,
  starts_at TIMESTAMP
);

Comparing across joins

SELECT * FROM events e JOIN schedules s ON e.occurred_at >= s.starts_at;

warning[E0033]: Comparing column 'events.occurred_at' (timestamp with time zone) with 'schedules.starts_at' (timestamp) depends on session TimeZone
  --> q.sql:1:44
    |
  1 | SELECT * FROM events e JOIN schedules s ON e.occurred_at >= s.starts_at;
    |                                            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    = help: Conversion between timestamp and timestamp with time zone shifts by session TimeZone. Use explicit AT TIME ZONE or CAST.

Assignment in UPDATE

UPDATE events SET local_at = occurred_at;

warning[E0033]: Assigning column 'events.occurred_at' (timestamp with time zone) to 'events.local_at' (timestamp) depends on session TimeZone
  --> q.sql:1:19
    |
  1 | UPDATE events SET local_at = occurred_at;
    |                   ^^^^^^^^
    = help: Conversion between timestamp and timestamp with time zone shifts by session TimeZone. Use explicit AT TIME ZONE or CAST.

How to fix

Make the timezone conversion explicit so the result does not depend on the session's connection setting:

• Use AT TIME ZONE to convert explicitly:

  SELECT * FROM events e JOIN schedules s ON (e.occurred_at AT TIME ZONE 'UTC') >= s.starts_at;
  UPDATE events SET local_at = (occurred_at AT TIME ZONE 'UTC');
  

• Or use an explicit CAST:

  SELECT * FROM events e JOIN schedules s ON e.occurred_at >= CAST(s.starts_at AS TIMESTAMPTZ);
  

Notes

• Literals (e.g. '2024-01-01 00:00:00') and functions like now() and CURRENT_TIMESTAMP are unaffected and stay silent.
• Explicit AT TIME ZONE expressions and CAST operations are explicit conversions and stay silent.
• Dialects where conversions are not session-dependent stay silent.

Configuring

# sqlsift.toml
[rules]
timestamp-timezone-mismatch = "error"   # or "off"

Or for a single line: -- sqlsift:disable E0033.