如何将含夏令时的本地Naive Datetime转换为Aware Datetime（欧洲柏林时区）

我有一个DataFrame，包含Date（本地日期字符串）、Time（本地时间字符串）和dst列，其中W代表冬季（夏令时未生效，与UTC时差为1小时），S代表夏季（夏令时生效，与UTC时差为2小时），时区为Europe/Berlin。需要将这些数据转换为UTC时区的Aware Datetime对象。

DataFrame示例：

Date      Time  dst
27.03.2022  01:15:00    W
27.03.2022  01:30:00    W
27.03.2022  01:45:00    W
27.03.2022  03:00:00    S
27.03.2022  03:15:00    S
27.03.2022  03:30:00    S
27.03.2022  03:45:00    S
27.03.2022  04:00:00    S
27.03.2022  04:15:00    S
27.03.2022  04:30:00    S
27.03.2022  04:45:00    S
27.03.2022  05:00:00    S
27.03.2022  05:15:00    S

第一种方法的问题 #

通过Pandas生成Datetime对象，本地化后根据dst列减去对应小时数：

from datetime import datetime, timedelta, timezone
from dateutil import tz

import numpy as np
import pandas as pd

df['datetime'] = pd.to_datetime(df['Date'] + df['Time'], format='%d.%m.%Y%H:%M:%S')
df['datetime_aware'] = df['datetime'].dt.tz_localize(tz='Europe/Berlin')
df['datetime_aware_subtracted'] = np.where(df['dst']=='S', df['datetime_aware']-timedelta(hours=2),
                                           df['datetime_aware']-timedelta(hours=1))

该方法在跨夏令时边界的时段（如03:00-05:00）结果错误，因为直接减去小时数会保留原时区偏移，导致转换后的UTC时间不正确。错误结果示例：

datetime               datetime_aware   datetime_aware_subtracted
27.03.2022 01:15    2022-03-27 01:15:00+01:00   2022-03-27 00:15:00+01:00
27.03.2022 01:30    2022-03-27 01:30:00+01:00   2022-03-27 00:30:00+01:00
27.03.2022 01:45    2022-03-27 01:45:00+01:00   2022-03-27 00:45:00+01:00
27.03.2022 03:00    2022-03-27 03:00:00+02:00   2022-03-27 00:00:00+01:00
27.03.2022 03:15    2022-03-27 03:15:00+02:00   2022-03-27 00:15:00+01:00
27.03.2022 03:30    2022-03-27 03:30:00+02:00   2022-03-27 00:30:00+01:00
27.03.2022 03:45    2022-03-27 03:45:00+02:00   2022-03-27 00:45:00+01:00
27.03.2022 04:00    2022-03-27 04:00:00+02:00   2022-03-27 01:00:00+01:00
27.03.2022 04:15    2022-03-27 04:15:00+02:00   2022-03-27 01:15:00+01:00
27.03.2022 04:30    2022-03-27 04:30:00+02:00   2022-03-27 01:30:00+01:00
27.03.2022 04:45    2022-03-27 04:45:00+02:00   2022-03-27 01:45:00+01:00
27.03.2022 05:00    2022-03-27 05:00:00+02:00   2022-03-27 03:00:00+02:00
27.03.2022 05:15    2022-03-27 05:15:00+02:00   2022-03-27 03:15:00+02:00

第二种方法的问题 #

先根据dst列减小时数，再进行本地化：

df['datetime'] = pd.to_datetime(df['Date'] + df['Time'], format='%d.%m.%Y%H:%M:%S')
df['datetime_subtracted'] = np.where(df['dst']=='S', df['datetime']-timedelta(hours=2),
                                     df['datetime']-timedelta(hours=1))
df['datetime_subtracted_aware'] = df['datetime_subtracted'].dt.tz_localize(tz='Europe/Berlin')

该方法生成的Naive Datetime结果正确，但本地化时触发NonExistentTimeError异常，因为夏令时切换时段（如2022-03-27 02:00:00）在Europe/Berlin时区不存在：

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\ProgramData\Miniconda3\envs\env\lib\site-packages\pandas\core\accessor.py", line 94, in f
    return self._delegate_method(name, *args, **kwargs)
  File "C:\ProgramData\Miniconda3\envs\env\lib\site-packages\pandas\core\indexes\accessors.py", line 123, in _delegate_method
    result = method(*args, **kwargs)
  File "C:\ProgramData\Miniconda3\envs\env\lib\site-packages\pandas\core\indexes\datetimes.py", line 273, in tz_localize
    arr = self._data.tz_localize(tz, ambiguous, nonexistent)
  File "C:\ProgramData\Miniconda3\envs\env\lib\site-packages\pandas\core\arrays\_mixins.py", line 84, in method
    return meth(self, *args, **kwargs)
  File "C:\ProgramData\Miniconda3\envs\env\lib\site-packages\pandas\core\arrays\datetimes.py", line 1043, in tz_localize
    new_dates = tzconversion.tz_localize_to_utc(
  File "pandas\_libs\tslibs\tzconversion.pyx", line 328, in pandas._libs.tslibs.tzconversion.tz_localize_to_utc
pytz.exceptions.NonExistentTimeError: 2022-03-27 02:00:00

最优解决方案 #

直接利用已知的dst信息，为每个本地时间指定对应的时区偏移，生成Aware Datetime后再转换为UTC。这种方法避开了夏令时自动转换的歧义问题：

import numpy as np
import pandas as pd
from datetime import timedelta, timezone

# 生成Naive本地Datetime
df['datetime'] = pd.to_datetime(df['Date'] + df['Time'], format='%d.%m.%Y%H:%M:%S')

# 根据dst列指定时区偏移，生成Aware本地Datetime
df['datetime_aware_local'] = np.where(
    df['dst'] == 'S',
    df['datetime'].dt.tz_localize(timezone(timedelta(hours=2))),  # 夏令时+02:00
    df['datetime'].dt.tz_localize(timezone(timedelta(hours=1)))   # 冬季+01:00
)

# 转换为UTC时区的Aware Datetime
df['datetime_utc'] = df['datetime_aware_local'].dt.tz_convert('UTC')

结果验证 #

执行后得到的datetime_utc列会正确对应UTC时间，例如：

• 2022-03-27 03:00:00+02:00（夏令时本地时间）转换为UTC是2022-03-27 01:00:00+00:00
• 2022-03-27 01:15:00+01:00（冬季本地时间）转换为UTC是2022-03-27 00:15:00+00:00

这种方法完全依赖已知的dst标记，不需要Pandas自动处理夏令时切换，避免了歧义或不存在时间的错误。

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