Message-ID: <68be1c2b7bcdbe3c110ee407ac6bbcf4f56df94a.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: Tue, 03 Mar 2026 13:30:17 +0100
In-Reply-To: <YT4P288MB0150006B32BC0AEE934E8ECA837FA@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
References: 
	<YT4P288MB01507D63F23F6B505AEB759E8375A@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
	 <8c3b8558fc3322c31d9f05517dcdb43e16296fa0.camel@cybertec.at>
	 <YT4P288MB0150AB8EEFF66C1DF6B8B32A837FA@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
	 <YT4P288MB0150006B32BC0AEE934E8ECA837FA@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/68be1c2b7bcdbe3c110ee407ac6bbcf4f56df94a.camel%40cybertec.at>
Precedence: bulk

On Tue, 2026-03-03 at 10:10 +0000, Nandish Bhuva wrote:
> As mentioned previously:
> =C2=A0* empjob_utc_update_date=C2=A0stores UTC values (but is defined as =
timestamp without time zone)
> =C2=A0* jstsk_lst_end_tm=C2=A0stores Canada/Pacific local time (also time=
stamp without time zone)
>
> Our goal is to convert both timestamps to a common timezone (for example,=
 UTC) within the query itself to ensure accurate comparison.
> I attempted the following:
>=20
> =C2=A0=C2=A0=C2=A01.=20
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0SELECT
> =C2=A0 =C2=A0 (to_timestamp('2026-02-19 01:23:46.016',
> =C2=A0 =C2=A0 'YYYY-MM-DD HH24:MI:SS.FF3')
> =C2=A0 =C2=A0 AT TIME ZONE 'Canada/Pacific')
> =C2=A0 =C2=A0 AT TIME ZONE 'UTC' AS utc_time;

to_timestamp() returns a "timestamp with time zone", so that won't work.

> =C2=A0=C2=A0=C2=A02.=20
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0select ej.empjob_utc_update_date=C2=
=A0,
>              (SELECT jstsk_lst_end_tm=C2=A0AT TIME ZONE 'Canada/Pacific' =
AT TIME ZONE 'UTC'
>               FROM jobskd_task
>               WHERE jstsk_desc=C2=A0=3D 'ALERT - VCH - Team approver Chan=
ges'
>                 AND jstsk_deleted=C2=A0=3D 'N'
>               order by jstsk_lst_end_tm
>               desc
>               limit 1) as job_last_run_time
>       from employee_job ej
>       where ej.empjob_utc_update_date=C2=A0>=3D (SELECT jstsk_lst_end_tm
>                                           FROM jobskd_task
>                                           WHERE jstsk_desc=C2=A0=3D 'ALER=
T - VCH - Team approver Changes'
>                                             AND jstsk_deleted=C2=A0=3D 'N=
'
>                                           limit 1)

The timezone conversion is done correctly, provided that "jstsk_lst_end_tm"
is really of data type "timestamp without time zone" and has "Canada/Pacifi=
c"
time stored.

> However, the result does not appear to be converting correctly in our act=
ual comparison scenario.

Please be precise.

What is the value stored, the value that the query returns, and what is
what you would consider the correct value?

> Could you please advise on the correct way to:
> =C2=A0=C2=A0=C2=A01. Treat empjob_utc_update_date=C2=A0explicitly as UTC
> =C2=A0=C2=A0=C2=A02. Treat jstsk_lst_end_tm=C2=A0explicitly as Canada/Pac=
ific

How do you treat a "timestamp without time zone" as UTC?
Please be specific.

> =C2=A0=C2=A0=C2=A03. Convert both to the same timezone (e.g., UTC) within=
 a SELECT=C2=A0query

That's what I showed you, and what you say is not correct.

> =C2=A0=C2=A0=C2=A04. Compare them accurately without modifying stored dat=
a

jstsk_lst_end_tm AT TIME ZONE 'Canada/Pacific' AT TIME ZONE 'UTC' =3D empjo=
b_utc_update_date


Yours,
Laurenz Albe