On Tue, 2026-03-03 at 10:10 +0000, Nandish Bhuva wrote:
> As mentioned previously:
> * empjob_utc_update_date stores UTC values (but is defined as timestamp
> without time zone)
> * jstsk_lst_end_tm stores Canada/Pacific local time (also timestamp 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:
>
> 1.
> SELECT
> (to_timestamp('2026-02-19 01:23:46.016',
> 'YYYY-MM-DD HH24:MI:SS.FF3')
> AT TIME ZONE 'Canada/Pacific')
> AT TIME ZONE 'UTC' AS utc_time;
to_timestamp() returns a "timestamp with time zone", so that won't work.
> 2.
> select ej.empjob_utc_update_date ,
> (SELECT jstsk_lst_end_tm AT TIME ZONE 'Canada/Pacific' AT TIME
> ZONE 'UTC'
> FROM jobskd_task
> WHERE jstsk_desc = 'ALERT - VCH - Team approver Changes'
> AND jstsk_deleted = '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 >= (SELECT jstsk_lst_end_tm
> FROM jobskd_task
> WHERE jstsk_desc = 'ALERT - VCH -
> Team approver Changes'
> AND jstsk_deleted = '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/Pacific"
time stored.
> However, the result does not appear to be converting correctly in our actual
> 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:
> 1. Treat empjob_utc_update_date explicitly as UTC
> 2. Treat jstsk_lst_end_tm explicitly as Canada/Pacific
How do you treat a "timestamp without time zone" as UTC?
Please be specific.
> 3. Convert both to the same timezone (e.g., UTC) within a SELECT query
That's what I showed you, and what you say is not correct.
> 4. Compare them accurately without modifying stored data
jstsk_lst_end_tm AT TIME ZONE 'Canada/Pacific' AT TIME ZONE 'UTC' =
empjob_utc_update_date
Yours,
Laurenz Albe
