You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by "adnanhamdussalam (via GitHub)" <gi...@apache.org> on 2026/04/09 12:15:29 UTC
[I] Time Zone Issue [cloudberry]
adnanhamdussalam opened a new issue, #1670:
URL: https://github.com/apache/cloudberry/issues/1670
Hi,
as we have daylight saving activity it was done at OS level but after that I am facing database time difference of one hour.
Database:
select now();
now
-------------------------------
2026-04-09 06:32:42.591684-05
show time zone;
TimeZone
---------------------
America/Mexico_City
OS:
timedatectl
Local time: Thu 2026-04-09 05:33:03 CST
Universal time: Thu 2026-04-09 11:33:03 UTC
RTC time: Thu 2026-04-09 11:33:03
Time zone: America/Mexico_City (CST, -0600)
After checking with the below query I am unable to find CST against time zone "America/Mexico_City"
select *from pg_timezone_names where abbrev='CST';
name | abbrev | utc_offset | is_dst
-----------------------+--------+------------+--------
ROC | CST | 08:00:00 | f
PRC | CST | 08:00:00 | f
Asia/Taipei | CST | 08:00:00 | f
Asia/Macao | CST | 08:00:00 | f
Asia/Macau | CST | 08:00:00 | f
Asia/Chongqing | CST | 08:00:00 | f
Asia/Chungking | CST | 08:00:00 | f
Asia/Harbin | CST | 08:00:00 | f
Asia/Shanghai | CST | 08:00:00 | f
Canada/Saskatchewan | CST | -06:00:00 | f
America/Regina | CST | -06:00:00 | f
America/Swift_Current | CST | -06:00:00 | f
America/Belize | CST | -06:00:00 | f
America/Costa_Rica | CST | -06:00:00 | f
America/El_Salvador | CST | -06:00:00 | f
America/Guatemala | CST | -06:00:00 | f
America/Tegucigalpa | CST | -06:00:00 | f
America/Managua | CST | -06:00:00 | f
I am using coordinator Cloudberry Version: 'PostgreSQL 14.4 (Apache Cloudberry 2.0.0-incubating build 1)
Need your support in this regard.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Time Zone Issue [cloudberry]
Posted by "my-ship-it (via GitHub)" <gi...@apache.org>.
my-ship-it commented on issue #1670:
URL: https://github.com/apache/cloudberry/issues/1670#issuecomment-4219410592
Hi @adnanhamdussalam, thanks for the follow-up and for providing the additional details!
After investigating this further, we've identified the **root cause**: Cloudberry is currently based on **PostgreSQL 14.4 (circa 2022)**, which ships with **tzdata version 2022a** (released March 2022). However, **Mexico officially abolished Daylight Saving Time in October 2022**, and this change was not included until **tzdata 2022f** (released October 2022). Since Cloudberry hasn't yet upgraded the bundled timezone data from the upstream PostgreSQL, the database still carries the outdated DST rules.
Because of this, the database still applies the old DST rules for `America/Mexico_City`, switching to CDT (UTC-5) every April — which is no longer correct in the real world. Your OS has the updated timezone data and correctly shows CST (UTC-6), so the 1-hour discrepancy you're seeing is expected given this mismatch.
**What the upstream fix looks like:**
PostgreSQL addressed this by updating the bundled tzdata. Specifically, the Mexico DST rule was changed from:
```
R m 2002 max - Apr Su>=1 2 1 D (DST forever)
```
to:
```
R m 2002 2022 - Apr Su>=1 2 1 D (DST ends in 2022)
```
This means after 2022, `America/Mexico_City` will correctly stay on CST (UTC-6) year-round — which is exactly what you need. The relevant upstream commits are available from tzdata 2022f through 2025b.
**Next steps:**
We have verified that these tzdata updates (2022f → 2025b) can be cleanly cherry-picked and will backport the fix to the `REL_2_STABLE` branch. The **2.1 release is currently being prepared**, and this fix will be included. We'll keep you posted on the progress.
Thanks again for reporting this — it's a great catch and will benefit other users in regions affected by recent DST law changes!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Time Zone Issue [cloudberry]
Posted by "my-ship-it (via GitHub)" <gi...@apache.org>.
my-ship-it commented on issue #1670:
URL: https://github.com/apache/cloudberry/issues/1670#issuecomment-4219465165
A draft PR has been submitted to backport the tzdata updates to the `REL_2_STABLE` branch: https://github.com/apache/cloudberry/pull/1674
Will keep you posted once it's merged.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Time Zone Issue [cloudberry]
Posted by "adnanhamdussalam (via GitHub)" <gi...@apache.org>.
adnanhamdussalam commented on issue #1670:
URL: https://github.com/apache/cloudberry/issues/1670#issuecomment-4214352690
Thank you for the update.
No I can not change it to any other timezone like 'America/Regina' because there are so many reporting teams that are doing reporting against it.
When I query the database, it shows abbrev CDT and utc_offset to -05 but I need abbrev CST and utc_offset to -06
select *from pg_timezone_names where name='America/Mexico_City';
name | abbrev | utc_offset | is_dst
---------------------+--------+------------+--------
America/Mexico_City | CDT | -05:00:00 | t
(1 row)
can you guide me how can I change to it?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Time Zone Issue [cloudberry]
Posted by "my-ship-it (via GitHub)" <gi...@apache.org>.
my-ship-it closed issue #1670: Time Zone Issue
URL: https://github.com/apache/cloudberry/issues/1670
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Time Zone Issue [cloudberry]
Posted by "my-ship-it (via GitHub)" <gi...@apache.org>.
my-ship-it commented on issue #1670:
URL: https://github.com/apache/cloudberry/issues/1670#issuecomment-4214196689
Hi @adnanhamdussalam, thanks for reporting this!
I took a look at this, and it seems like the difference you're seeing might be related to a **timezone data (`tzdata`) version mismatch** between your OS and the database, rather than a Cloudberry issue itself.
Here's what I think might be happening:
- The `America/Mexico_City` timezone normally observes **Daylight Saving Time (DST)**, switching from **CST (UTC-6)** to **CDT (UTC-5)** when DST is active.
- Your database shows `-05` (CDT/UTC-5), which would suggest it believes DST is currently in effect.
- Your OS, on the other hand, still shows `CST (UTC-6)`, which could mean its `tzdata` package has different or older DST rules.
- Interestingly, if you convert both times to UTC, they appear to agree: DB → `06:32 - 5h = 11:32 UTC`, OS → `05:33 - 6h = 11:33 UTC`. The small ~1 min gap is likely just the time between running the two commands.
So the absolute time may actually be consistent — the difference seems to be in which UTC offset each system thinks is current.
**A few things you could try:**
1. **Check your OS `tzdata` version** and consider updating it to make sure it has the latest DST rules.
2. **Verify the database UTC time** by running:
```sql
SELECT now() AT TIME ZONE 'UTC';
```
This can help confirm whether the database's absolute time is correct.
3. **If DST isn't needed for your use case**, you could set a fixed-offset timezone that doesn't observe DST, for example:
```sql
SET timezone = 'America/Regina'; -- always CST/UTC-6, no DST
```
I hope this points you in the right direction! If this doesn't resolve the issue or if I've misunderstood something, please don't hesitate to share more details and we'll be happy to dig deeper. 😊
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [I] Time Zone Issue [cloudberry]
Posted by "my-ship-it (via GitHub)" <gi...@apache.org>.
my-ship-it commented on issue #1670:
URL: https://github.com/apache/cloudberry/issues/1670#issuecomment-4257548219
The tzdata update has been merged via #1674. The bundled timezone data is now updated to **tzdata 2025b**, which includes the fix for `America/Mexico_City` (Mexico DST abolishment in 2022) along with all other timezone changes up to 2025b.
This fix will be available in the upcoming 2.1 release. Closing this issue.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org