public inbox for [email protected]
help / color / mirror / Atom feedFrom: Laurenz Albe <[email protected]>
To: Nandish Bhuva <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: Timezone handling with timestamp without time zone columns
Date: Tue, 03 Mar 2026 13:30:17 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <YT4P288MB0150006B32BC0AEE934E8ECA837FA@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
References: <YT4P288MB01507D63F23F6B505AEB759E8375A@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
<[email protected]>
<YT4P288MB0150AB8EEFF66C1DF6B8B32A837FA@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
<YT4P288MB0150006B32BC0AEE934E8ECA837FA@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Timezone handling with timestamp without time zone columns
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox