Thread

1. Timezone handling with timestamp without time zone columns

   Nandish Bhuva <nandish.bhuva@srmsoftwareinc.com> — 2026-02-25T08:28:28Z

   Hi Team,
   I would like to report a timezone-related issue we are encountering in our PostgreSQL database.
   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.
     *
   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
     2.
   Give me best solution for without even changing the column datatype.
   
   Please advise on the best practice to ensure consistent timezone handling and accurate comparisons going forward.
   Thanks in advance for your support.
   
   
   Regards,
   Nandish Bhuva
   [cid:ba34352d-4c57-4251-9073-5b946953cdbf]
   

2. Re: Timezone handling with timestamp without time zone columns

   Laurenz Albe <laurenz.albe@cybertec.at> — 2026-02-27T15:01:10Z

   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
   
   
   
   

3. Re: Timezone handling with timestamp without time zone columns

   Peter J. Holzer <hjp-pgsql@hjp.at> — 2026-02-28T12:00:54Z

   On 2026-02-27 16:01:10 +0100, Laurenz Albe wrote:
   > The best practice is that you store tmestamps in a consistent fashion:
   
   ACK.
   
   
   > 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
   
   Be aware though that in timezones with DST there is one hour in autumn
   which cannot be properly represented:
   
   hjp=> set timezone = 'Canada/Pacific';
   SET
   
   hjp=> select '2025-11-02 08:23Z'::timestamptz::timestamp;
   ╔═════════════════════╗
   ║      timestamp      ║
   ╟─────────────────────╢
   ║ 2025-11-02 01:23:00 ║
   ╚═════════════════════╝
   (1 row)
   
   hjp=> select '2025-11-02 09:23Z'::timestamptz::timestamp;
   ╔═════════════════════╗
   ║      timestamp      ║
   ╟─────────────────────╢
   ║ 2025-11-02 01:23:00 ║
   ╚═════════════════════╝
   (1 row)
   
   (OTOH sometimes you need the local time without any reference to a time
   zone or at least without a UTC offset)
   
           hjp
   
   -- 
      _  | Peter J. Holzer    | Story must make more sense than reality.
   |_|_) |                    |
   | |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
   __/   | http://www.hjp.at/ |       challenge!"
   

4. Re: Timezone handling with timestamp without time zone columns

   Nandish Bhuva <nandish.bhuva@srmsoftwareinc.com> — 2026-03-03T10:10:15Z

   Adding pgsql-general@lists.postgresql.org +++
   
   Regards,
   Nandish Bhuva
   [cid:299ed884-fec4-4f78-a205-4c85638d25c6]
   ________________________________
   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:
   
   
     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 <laurenz.albe@cybertec.at>
   Sent: Friday, February 27, 2026 8:31 PM
   To: Nandish Bhuva <Nandish.bhuva@srmsoftwareinc.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
   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
   

5. Re: Timezone handling with timestamp without time zone columns

   Laurenz Albe <laurenz.albe@cybertec.at> — 2026-03-03T12:30:17Z

   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
   
   
   
   

6. Re: Timezone handling with timestamp without time zone columns

   Adrian Klaver <adrian.klaver@aklaver.com> — 2026-03-03T15:53:53Z

   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