如何将PST时区的Epoch秒时间转换为UTC时间并处理夏令时

Got it, let's break this down and fix your timezone conversion issue properly—including daylight saving time (DST) handling. The core problem here is that PST is a static timezone abbreviation (it only represents UTC-8, with no awareness of DST), but the actual timezone you need to use for the US West Coast is America/Los_Angeles—this identifier automatically accounts for both PST (standard time) and PDT (daylight saving time) transitions.

Step-by-Step Solution (assuming you're using BigQuery, since you mentioned TIMESTAMP_SECONDS) #

First, let's clarify the context: Your stored epoch seconds are being converted to UTC via TIMESTAMP_SECONDS(1619067630) which gives 2021-04-22 05:00:30 UTC, and this UTC time maps to the correct local time in the PST/PDT timezone. To properly convert between this local timezone and UTC while handling DST, follow these steps:

1. Convert UTC timestamp to America/Los_Angeles time (with DST)
   If you need to get the correct local time (accounting for DST) from your UTC timestamp, use the TIME_ZONE function with the full timezone identifier:

   TIME_ZONE(TIMESTAMP_SECONDS(1619067630), 'America/Los_Angeles')
   

   For your example value 1619067630, this will return 2021-04-21 22:00:30 PDT—since April falls in DST for the US West Coast, it automatically uses the UTC-7 offset instead of the static UTC-8 for PST.

2. Convert local America/Los_Angeles epoch seconds to UTC
   If your stored epoch seconds are based on the local America/Los_Angeles time (not UTC-based epoch seconds), you need to explicitly tell BigQuery the timezone to correctly map it to UTC:

   TIMESTAMP(DATETIME_SECONDS(your_epoch_column), 'America/Los_Angeles')
   

   This function will apply the correct offset (either UTC-8 or UTC-7) based on whether the local time falls in standard or daylight saving time.

Critical Notes #

• Always use full timezone identifiers: Avoid abbreviations like PST or PDT—they don't carry DST rules. Use IANA timezone identifiers like America/Los_Angeles, Europe/Paris, etc., which include all historical and future DST transition data.
• Confirm your epoch's baseline: Make sure you know whether your stored seconds are UTC-based epoch (standard) or local-time-based epoch. If it's local, the DATETIME_SECONDS + TIMESTAMP combo above is your best bet.

Example Check #

For your sample 1619067630:

• TIMESTAMP_SECONDS(1619067630) → 2021-04-22 05:00:30 UTC (standard UTC timestamp)
• TIME_ZONE(..., 'America/Los_Angeles') → 2021-04-21 22:00:30 PDT (correct DST-aware local time)
• To convert that local PDT time back to UTC, just use the base TIMESTAMP value—BigQuery timestamps are inherently UTC under the hood.

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