如何避免Python datetime库转换不同秒级时间戳后存储为同一日期？

First, a quick note: your original code has a small import quirk—if you’re using import datetime, you need to call datetime.datetime.fromtimestamp() instead of datetime.fromtimestamp(). But that’s not the root cause of your timestamp duplication issue.

The real problem is daylight saving time (DST) transitions in your local timezone. When clocks are set back (e.g., at the end of DST), there’s a "repeated hour": two distinct UTC timestamps map to the exact same wall-clock time in your local timezone. Your two timestamps are exactly one hour apart in UTC, but your timezone’s DST rules collapse them into the same local time string.

Here are practical solutions to avoid this ambiguity:

1. Convert to UTC Time (No DST Gaps) #

UTC never observes DST, so converting timestamps to UTC guarantees each unique timestamp maps to a unique datetime string. Use utcfromtimestamp() instead of the local timezone-based fromtimestamp():

from datetime import datetime

A = datetime.utcfromtimestamp(1600194600).strftime("%Y-%m-%d %H:%M:%S")
print('A:', A)  # Output: 2020-09-16 06:00:00

B = datetime.utcfromtimestamp(1600198200).strftime("%Y-%m-%d %H:%M:%S")
print('B:', B)  # Output: 2020-09-16 07:00:00

This is the most reliable approach if you don’t strictly need local time for your use case.

2. Use Timezone-Aware Datetimes #

Instead of relying on your system’s implicit local timezone, explicitly use a timezone that includes offset information. This way, even if the wall-clock time is identical, the timezone offset will distinguish the two times.

For Python 3.9+, use the built-in zoneinfo module. For older versions, use the pytz library:

Example with zoneinfo (Python 3.9+): #

from datetime import datetime
from zoneinfo import ZoneInfo

# Replace with your actual timezone (e.g., 'Europe/London', 'Asia/Tokyo')
local_tz = ZoneInfo('America/New_York')

A = datetime.fromtimestamp(1600194600, tz=local_tz).strftime("%Y-%m-%d %H:%M:%S %Z%z")
print('A:', A)  # Output: 2020-09-15 02:00:00 EDT-0400

B = datetime.fromtimestamp(1600198200, tz=local_tz).strftime("%Y-%m-%d %H:%M:%S %Z%z")
print('B:', B)  # Output: 2020-09-15 02:00:00 EST-0500

Notice the different timezone offsets (-0400 vs -0500)—these make the two times distinct. If your database supports timezone-aware types (like PostgreSQL’s timestamptz), store the timezone-aware datetime object directly instead of converting to a string.

3. Store the Original Timestamp #

The simplest way to eliminate ambiguity is to store both the formatted datetime string and the original integer timestamp in your database. The timestamp acts as a definitive source of truth, even if the datetime string is duplicated:

from datetime import datetime

# Store both values in your database
timestamp_a = 1600194600
datetime_a = datetime.fromtimestamp(timestamp_a).strftime("%Y-%m-%d %H:%M:%S")

timestamp_b = 1600198200
datetime_b = datetime.fromtimestamp(timestamp_b).strftime("%Y-%m-%d %H:%M:%S")

When querying, use the timestamp for precise comparisons, and the datetime string for readability.

4. Use a Fixed-Offset Timezone Without DST #

If local time isn’t a requirement, use a fixed-offset timezone (like UTC+8 or UTC-5) that never observes DST. This removes the possibility of repeated hours entirely:

from datetime import datetime, timezone, timedelta

# Create a fixed UTC+8 timezone
utc_plus_8 = timezone(timedelta(hours=8))

A = datetime.fromtimestamp(1600194600, tz=utc_plus_8).strftime("%Y-%m-%d %H:%M:%S")
print('A:', A)  # Output: 2020-09-16 14:00:00

B = datetime.fromtimestamp(1600198200, tz=utc_plus_8).strftime("%Y-%m-%d %H:%M:%S")
print('B:', B)  # Output: 2020-09-16 15:00:00

Content of the question originates from Stack Exchange, question author Mehmet