Feedback-ID: i76984098:Fastmail
Message-ID: <03158640-b768-4d9f-90ce-6a30029febc2@aklaver.com>
Date: Tue, 3 Mar 2026 07:53:53 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Timezone handling with timestamp without time zone columns
To: Nandish Bhuva <Nandish.bhuva@srmsoftwareinc.com>,
 Laurenz Albe <laurenz.albe@cybertec.at>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <YT4P288MB01507D63F23F6B505AEB759E8375A@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
 <8c3b8558fc3322c31d9f05517dcdb43e16296fa0.camel@cybertec.at>
 <YT4P288MB0150AB8EEFF66C1DF6B8B32A837FA@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
 <YT4P288MB0150006B32BC0AEE934E8ECA837FA@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <YT4P288MB0150006B32BC0AEE934E8ECA837FA@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/03158640-b768-4d9f-90ce-6a30029febc2%40aklaver.com>
Precedence: bulk

On 3/3/26 2:10 AM, Nandish Bhuva wrote:
> Adding pgsql-general@lists.postgresql.org +++
> 
> Regards,
> Nandish Bhuva
> ------------------------------------------------------------------------
> *From:* Nandish Bhuva <Nandish.bhuva@srmsoftwareinc.com>
> *Sent:* Tuesday, March 3, 2026 3:01 PM
> *To:* Laurenz Albe <laurenz.albe@cybertec.at>
> *Subject:* Re: Timezone handling with timestamp without time zone columns
> @Laurenz Able <mailto:laurenz.albe@cybertec.at>
> 
> 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
adrian.klaver@aklaver.com