如何在Pandas中将Timestamp列转为无时区格式适配SQLite3？

Got it, let's break this down clearly—since you've already converted your DataFrame's time column to UTC, stripping the timezone marker to get a "naive" datetime (which plays nice with SQLite3) is straightforward. Here's exactly what you need to do:

Step 1: Confirm Your Column is UTC-Timezone-Aware #

First, double-check that your datetime column is indeed in UTC. Run this to verify:

print(df['your_time_column'].dt.tz)

You should see UTC printed if it's correctly timezone-aware.

Step 2: Strip the Timezone Information #

Use Pandas' tz_localize(None) method on the datetime series to remove the timezone marker. This converts your timezone-aware UTC datetime to a naive datetime (no timezone attached) while keeping the UTC timestamp values intact.

Basic Implementation: #

# Replace 'your_time_column' with your actual column name
df['your_time_column'] = df['your_time_column'].dt.tz_localize(None)

If Your Column Isn't Already UTC-Aware (Just in Case): #

If for some reason you haven't converted to UTC yet, first convert to UTC then strip the timezone:

# Replace 'original_timezone' with your column's current timezone (e.g., 'Asia/Shanghai')
df['your_time_column'] = df['your_time_column'].dt.tz_convert('UTC').dt.tz_localize(None)

Step 3: Verify the Result #

After running the code, confirm the timezone is gone with:

print(df['your_time_column'].dt.tz)

This should return None, meaning you now have a naive datetime column that's compatible with SQLite3.

Example with Sample Data #

Let's walk through a quick example to make it concrete:

import pandas as pd

# Sample timezone-aware UTC data
data = {'timestamp': ['2024-05-20 12:00:00+00:00', '2024-05-20 13:00:00+00:00']}
df = pd.DataFrame(data)
df['timestamp'] = pd.to_datetime(df['timestamp'], utc=True)

# Strip timezone info
df['timestamp'] = df['timestamp'].dt.tz_localize(None)

# Check result
print(df['timestamp'])
print("Timezone after stripping:", df['timestamp'].dt.tz)

Why This Works for SQLite3 #

SQLite3's DATETIME type doesn't support storing timezone information. If you try to write a timezone-aware datetime to SQLite, you'll likely get warnings or errors. By converting to a naive UTC datetime, you preserve the correct UTC timestamp values without the timezone marker, which SQLite can handle perfectly.

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