Time Zone Issue

[adnanhamdussalam]

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.

[my-ship-it]

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:

   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:

   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. 😊

[adnanhamdussalam]

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)

OS package tzdata has already been to latest version.

can you guide me how can I change to it?

[my-ship-it]

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!

[my-ship-it]

A draft PR has been submitted to backport the tzdata updates to the REL_2_STABLE branch: #1674

Will keep you posted once it's merged.