SqlAlchemy存入MySQL的DateTime与pendulum解析日期的时间戳不一致问题排查

Let's break down exactly why those two timestamps differ by one hour—it all boils down to timezone handling mismatches between Pendulum, SQLAlchemy, and MySQL.

The Root Cause #

Here's the step-by-step chain of events leading to the discrepancy:

1. Original Parsing: When you run pendulum.parse('2015-03-09'), Pendulum creates a timezone-aware datetime object using your system's local timezone (e.g., UTC+1 for Central European Time). For example, this would be 2015-03-09 00:00:00 UTC+1, whose timestamp is 1425855600 (since UTC+1 midnight is equivalent to UTC 23:00 on March 8).

2. Storing in MySQL: MySQL's DateTime column type doesn't store timezone information—it only holds a naive datetime (no offset). SQLAlchemy converts your timezone-aware Pendulum object to a naive datetime, usually using the database connection's timezone (often UTC). So your local midnight gets converted to UTC 23:00 March 8 and stored as that naive value.

3. Retrieving the Value: When you load the date from MySQL, you get back the naive datetime 2015-03-08 23:00:00. If you convert this to a Pendulum object without explicitly specifying a timezone, Pendulum may assume it's in UTC (depending on your setup), resulting in a timestamp of 1425855600.

4. Re-Parsing the String: If you run pendulum.parse('2015-03-09') again later, it uses your local timezone again, creating 2015-03-09 00:00:00 UTC+1—but wait, that should have the same timestamp as the original, right? Wait no—if your system timezone changed, or you set Pendulum's default timezone to UTC (via pendulum.set_default_timezone('UTC')), this parse would create 2015-03-09 00:00:00 UTC with a timestamp of 1425859200, which is exactly the difference you're seeing.

Other possible contributors:

• MySQL TIMESTAMP vs DATETIME: If your column is actually TIMESTAMP (not DATETIME), MySQL automatically converts values between the server's timezone and UTC, which can shift the datetime by an hour.
• DST Transition: March 9, 2015, falls right after DST starts in many regions (e.g., US DST started March 8). If your timezone observes DST, a shift in offset could cause this one-hour difference.

How to Fix It #

To ensure consistent timestamps, you need to standardize timezone handling:

1. Store All Dates in UTC: Convert Pendulum objects to UTC before storing to eliminate timezone confusion:

   # Convert to UTC before saving
   utc_date = pendulum.parse('2015-03-09').in_timezone('UTC')
   session.add(MyModel(created=utc_date))
   

2. Explicitly Set Timezone When Retrieving: When loading from the database, convert the naive datetime back to UTC:

   obj = session.query(MyModel).first()
   created = pendulum.instance(obj.created).in_timezone('UTC')
   

3. Use Timezone-Aware Columns (MySQL 8.0+): If your MySQL version supports it, use SQLAlchemy's DateTime(timezone=True) type to preserve timezone information directly in the database.

4. Verify Column Type: Double-check that your MySQL column is DATETIME (not TIMESTAMP) if you don't want automatic timezone conversion. Run DESCRIBE mytable; in MySQL to confirm.

With these changes, your timestamp comparison will return True every time.

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