Re: Timezone handling with timestamp without time zone columns

On Wed, 2026-02-25 at 08:28 +0000, Nandish Bhuva wrote:
> I would like to report a timezone-related issue we are encountering in our PostgreSQL database.

To avoid misunderstandings: thsi is not a problem of PostgreSQL, but a user-created
problem, right?

> We have two columns:
>  * empjob_utc_update_date
>  * jstsk_lst_end_tm
>
> Both columns are defined as timestamp without time zone.
> Currently, we are observing the following values:
>  * empjob_utc_update_date → 2026-02-19 06:26:23.830811
>  * jstsk_lst_end_tm → 2026-02-19 01:23:46.016
>
> Our entire application runs in the Canada/Pacific timezone. However, when comparing
> these two timestamps in our queries, we are getting incorrect results in the system.
> It appears that:
>  * empjob_utc_update_date is effectively storing UTC time.
>  * jstsk_lst_end_tm is storing Canada/Pacific local time.

To reiterate: *you* are storing the data in the columns in this way.

>  * Since both columns are defined as timestamp without time zone, PostgreSQL does not
> apply any timezone conversion during comparison, which is leading to logical
> inconsistencies.
>
> We would like clarification on the recommended approach to handle this scenario. Specifically:
>    1. Should both columns be converted to timestamp with time zone

If you are operating only within a single time zone, it doesn't matter.
You just have to be consistent about how you store timestamps.

>    2. Give me best solution for without even changing the column datatype.

You can fix the incorrectly stored data with

UPDATE tab
SET empjob_utc_update_date =
empjob_utc_update_date AT TIME ZONE 'UTC'
AT TIME ZONE 'America/Chicago';

That will convert UTC timestamps to Chicago timestamps.

> Please advise on the best practice to ensure consistent timezone handling and accurate
> comparisons going forward.

The best practice is that you store tmestamps in a consistent fashion:

either

- use "timestamp with time zone", store timestamps with time zone
and make sure that the parameter "timezone" is set correctly in each
database session

or

- use "timestamp without time zone" and store only Chicago timestamps
without a time zone

Yours,
Laurenz Albe