Timestamps Appear Offset From Source When Querying Data

Issue

When querying data in the destination, timestamps appear offset from the corresponding source values.

Environment

Destination: PostgreSQL

Resolution

To resolve this issue, configure your PostgreSQL sessions to display timestamps in UTC when querying. Use one of the following options:

• Set your current session's time zone to UTC and rerun your query:

  SET TIME ZONE 'UTC';
  SELECT ...;
  

• Convert a specific field within a query:

  SELECT field AT TIME ZONE 'UTC' FROM ...;
  

• Use UTC as the default time zone by setting the following in your postgresql.conf file:

  timezone = 'UTC'
  

Cause

This issue occurs when you query your PostgreSQL destination using a session that is set to a time zone other than UTC.

PostgreSQL stores TIMESTAMP WITH TIME ZONE (timestamptz) values in UTC. However, when you run a query, PostgreSQL converts those values to your session's configured time zone before returning the results.