PostgreSQL多时区转CST：字符串时间戳处理疑问

Great question—this is a common pitfall when dealing with mixed time zone data, and yes, your current approach will cause issues if you have both CST and IST timestamps in the table. Let's break this down and fix it properly.

Why Your Current Query Fails #

Your existing code hardcodes the final time zone to CST:

SELECT ((timestamp '2015-10-24 16:38:46') AT TIME ZONE 'UTC') AT TIME ZONE 'CST';

This assumes every timestamp in your table originates from UTC, but if some are actually in IST, converting them to CST directly will completely skew their true value (IST is ~12.5 hours ahead of CST, for example). You need a way to dynamically handle the time zone associated with each row.

Solutions Based on Your Data Structure #

There are two common scenarios for mixed time zone data—here's how to handle each:

Scenario 1: Timestamp Strings Include Time Zone Suffixes #

If your timestamp strings look like '2015-10-24 16:38:46 CST' or '2024-05-20 10:00:00 IST' (with the timezone embedded), use PostgreSQL's TO_TIMESTAMP_TZ function to parse the full string directly:

CREATE VIEW formatted_timestamps AS
SELECT 
  TO_TIMESTAMP_TZ(your_timestamp_column, 'YYYY-MM-DD HH24:MI:SS TZ') AS converted_timestamptz
FROM your_table;

• Pro tip: Time zone abbreviations like CST can be ambiguous (it can mean China Standard Time or US Central Standard Time). To avoid parsing errors, use full, unambiguous time zone names like 'Asia/Shanghai' (for China CST) or 'Asia/Kolkata' (for IST) whenever possible.

Scenario 2: Timestamps Are Raw Strings + Separate Time Zone Column #

If your table has a raw timestamp column (e.g., '2015-10-24 16:38:46') and a separate column (e.g., timezone_code) storing 'CST' or 'IST', dynamically apply the time zone per row:

CREATE VIEW formatted_timestamps AS
SELECT 
  -- First convert the string to a naive timestamp, treat it as UTC, then convert to the row's time zone
  your_timestamp_column::timestamp AT TIME ZONE 'UTC' AT TIME ZONE timezone_code AS converted_timestamptz
FROM your_table;

• Note: Adjust the initial AT TIME ZONE 'UTC' if your raw timestamps are stored in a different base time zone (e.g., if they're already in local CST/IST, skip that step and just use your_timestamp_column::timestamp AT TIME ZONE timezone_code).

Best Practices for Long-Term Fixes #

• Standardize storage: Whenever possible, store timestamps as TIMESTAMPTZ (PostgreSQL's time zone-aware timestamp type) instead of strings. This eliminates conversion errors entirely and makes time zone operations trivial.
• Avoid ambiguous abbreviations: Stick to full time zone names from the IANA Time Zone Database (e.g., 'Asia/Kolkata' instead of 'IST') to prevent misinterpretation.

内容的提问来源于stack exchange，提问作者gklaxman