public inbox for [email protected]  

help / color / mirror / Atom feed

From: 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: Fri, 27 Feb 2026 16:01:10 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <YT4P288MB01507D63F23F6B505AEB759E8375A@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
References: <YT4P288MB01507D63F23F6B505AEB759E8375A@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>

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

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