Re: Timezone handling with timestamp without time zone columns

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Nandish Bhuva <[email protected]>
To: Laurenz Albe <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: Timezone handling with timestamp without time zone columns
Date: Tue, 3 Mar 2026 10:10:15 +0000
Message-ID: <YT4P288MB0150006B32BC0AEE934E8ECA837FA@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <YT4P288MB0150AB8EEFF66C1DF6B8B32A837FA@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
References: <YT4P288MB01507D63F23F6B505AEB759E8375A@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
	<[email protected]>
	<YT4P288MB0150AB8EEFF66C1DF6B8B32A837FA@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>

Adding [email protected] +++

Regards,
Nandish Bhuva
[cid:299ed884-fec4-4f78-a205-4c85638d25c6]
________________________________
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:

  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;

  1.
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
)

However, the result does not appear to be converting correctly in our actual comparison scenario.
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
  3.
Convert both to the same timezone (e.g., UTC) within a SELECT query
  4.
Compare them accurately without modifying stored data

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.
Regards,
Nandish Bhuva
[cid:cf0a551d-7171-499c-bc85-17b504e00d19]
________________________________
From: Laurenz Albe <[email protected]>
Sent: Friday, February 27, 2026 8:31 PM
To: Nandish Bhuva <[email protected]>; [email protected] <[email protected]>
Subject: Re: Timezone handling with timestamp without time zone columns

On Wed, 2026-02-25 at 08:28 +0000, Nandish Bhuva wrote:
> I would like to report a timezone-related issue we are encountering in our PostgreSQL database.

To avoid misunderstandings: thsi is not a problem of PostgreSQL, but a user-created
problem, right?

> We have two columns:
>  * empjob_utc_update_date
>  * jstsk_lst_end_tm
>
> Both columns are defined as timestamp without time zone.
> Currently, we are observing the following values:
>  * empjob_utc_update_date → 2026-02-19 06:26:23.830811
>  * jstsk_lst_end_tm → 2026-02-19 01:23:46.016
>
> Our entire application runs in the Canada/Pacific timezone. However, when comparing
> these two timestamps in our queries, we are getting incorrect results in the system.
> It appears that:
>  * empjob_utc_update_date is effectively storing UTC time.
>  * jstsk_lst_end_tm is storing Canada/Pacific local time.

To reiterate: *you* are storing the data in the columns in this way.

>  * Since both columns are defined as timestamp without time zone, PostgreSQL does not
>    apply any timezone conversion during comparison, which is leading to logical
>    inconsistencies.
>
> We would like clarification on the recommended approach to handle this scenario. Specifically:
>    1. Should both columns be converted to timestamp with time zone

If you are operating only within a single time zone, it doesn't matter.
You just have to be consistent about how you store timestamps.

>    2. Give me best solution for without even changing the column datatype.

You can fix the incorrectly stored data with

  UPDATE tab
  SET empjob_utc_update_date =
      empjob_utc_update_date AT TIME ZONE 'UTC'
                             AT TIME ZONE 'America/Chicago';

That will convert UTC timestamps to Chicago timestamps.

> Please advise on the best practice to ensure consistent timezone handling and accurate
> comparisons going forward.

The best practice is that you store tmestamps in a consistent fashion:

either

- use "timestamp with time zone", store timestamps with time zone
  and make sure that the parameter "timezone" is set correctly in each
  database session

or

- use "timestamp without time zone" and store only Chicago timestamps
  without a time zone

Yours,
Laurenz Albe

Attachments:

  [image/png] Outlook-wnw0kql4.png (4.7K, 3-Outlook-wnw0kql4.png)
  download | view image

  [image/png] Outlook-vgctuoen.png (4.7K, 4-Outlook-vgctuoen.png)
  download | view image

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: <YT4P288MB0150006B32BC0AEE934E8ECA837FA@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>

* 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