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
accuratecomparison.
> 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
