On 3/3/26 2:10 AM, Nandish Bhuva wrote:
Adding [email protected] +++
Regards,
Nandish Bhuva
------------------------------------------------------------------------
*From:* Nandish Bhuva <[email protected]>
*Sent:* Tuesday, March 3, 2026 3:01 PM
*To:* Laurenz Albe <[email protected]>
*Subject:* Re: Timezone handling with timestamp without time zone columns
@Laurenz Able <mailto:[email protected]>
Thank you for your response and for clarifying that the issue stems from
how the timestamps are being stored rather than from PostgreSQL itself.
Unfortunately, the application is quite large and complex, and at this
time we are not in a position to modify the column definitions or update
the stored data. Therefore, we are looking for a solution that allows us
to handle the timezone conversion purely at the query level using |
SELECT|, without altering the table structure or existing data.
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:
Your guidance on the proper |AT TIME ZONE| usage for |timestamp without
time zone| columns would be greatly appreciated.
Thank you again for your assistance.
1) I would strongly suggest you read:
https://www.postgresql.org/docs/current/datatype-datetime.html
8.5.1.3. Time Stamps
2) Assuming the server is set to Canada/Pacific time:
-- My Ubuntu instance does not have Canada/Pacific
set timezone = 'America/Vancouver';
select '2025-03-03 07:44'::timestamp, ('2025-03-03 15:44'::timestamp AT
time zone 'UTC')::timestamp;
timestamp | timezone
---------------------+---------------------
2025-03-03 07:44:00 | 2025-03-03 07:44:00
Where the first timestamp is just left alone as it is in local time and
the second is defined as being at UTC and then rotated to local time and
has the time zone offset stripped off by the cast to timestamp.
Regards,
Nandish Bhuva
Yours,
Laurenz Albe
--
Adrian Klaver
[email protected]
