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
