Message-ID: <8c3b8558fc3322c31d9f05517dcdb43e16296fa0.camel@cybertec.at>
Subject: Re: Timezone handling with timestamp without time zone columns
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Nandish Bhuva <Nandish.bhuva@srmsoftwareinc.com>, 
 "pgsql-general@lists.postgresql.org"
	 <pgsql-general@lists.postgresql.org>
Date: Fri, 27 Feb 2026 16:01:10 +0100
In-Reply-To: <YT4P288MB01507D63F23F6B505AEB759E8375A@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
References: 
	<YT4P288MB01507D63F23F6B505AEB759E8375A@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/8c3b8558fc3322c31d9f05517dcdb43e16296fa0.camel%40cybertec.at>
Precedence: bulk

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 ou=
r PostgreSQL database.

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

> We have two columns:
> =C2=A0* empjob_utc_update_date
> =C2=A0* jstsk_lst_end_tm
>
> Both columns are defined as=C2=A0timestamp without time zone.
> Currently, we are observing the following values:
> =C2=A0* empjob_utc_update_date =E2=86=92=C2=A02026-02-19 06:26:23.830811
> =C2=A0* jstsk_lst_end_tm =E2=86=92=C2=A02026-02-19 01:23:46.016
>
> Our entire application runs in the=C2=A0Canada/Pacific=C2=A0timezone. How=
ever, when comparing
> these two timestamps in our queries, we are getting incorrect results in =
the system.
> It appears that:
> =C2=A0* empjob_utc_update_date=C2=A0is effectively storing UTC time.
> =C2=A0* jstsk_lst_end_tm=C2=A0is storing Canada/Pacific local time.

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

> =C2=A0* Since both columns are defined as=C2=A0timestamp without time zon=
e, PostgreSQL does not
>    apply any timezone conversion during comparison, which is leading to l=
ogical
>    inconsistencies.
>
> We would like clarification on the recommended approach to handle this sc=
enario. Specifically:
> =C2=A0=C2=A0=C2=A01. Should both columns be converted to=C2=A0timestamp w=
ith 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.

> =C2=A0=C2=A0=C2=A02. 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 =3D
      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