如何在Python中对比SQLite数据库日期字段/TEXT类型日期与当前日期？

Hey there! Since you've already got sqlite3 and datetime set up in your Python environment, let's tackle your two date comparison questions with SQLite clearly:

1. How to compare the current date with a date field in an SQLite table #

SQLite doesn’t have a native DATE data type, but dates are commonly stored as TEXT (in ISO 8601 format like YYYY-MM-DD), REAL (Julian day numbers), or INTEGER (Unix timestamps). You can compare dates in two straightforward ways:

Option 1: Use SQLite's built-in date functions directly in queries #

SQLite has handy functions like DATE('now') that return the current date in YYYY-MM-DD format. You can use this directly in your WHERE clause:

import sqlite3

conn = sqlite3.connect("your_database.db")
cursor = conn.cursor()

# Fetch records where the date column matches today's date
cursor.execute("SELECT * FROM your_table WHERE date_column = DATE('now')")
matching_records = cursor.fetchall()

conn.close()

Option 2: Generate the current date in Python and pass it as a parameter #

This is great for parameterized queries (safer against SQL injection):

import sqlite3
from datetime import date

conn = sqlite3.connect("your_database.db")
cursor = conn.cursor()

# Get today's date as an ISO 8601 string (YYYY-MM-DD)
today = date.today().isoformat()

# Use a parameterized query to compare
cursor.execute("SELECT * FROM your_table WHERE date_column = ?", (today,))
matching_records = cursor.fetchall()

conn.close()

If your date is stored as a Unix timestamp (INTEGER), adjust the approach to target the full day range:

from datetime import datetime

# Get timestamp for today's midnight and midnight tomorrow
today_start = datetime.combine(date.today(), datetime.min.time()).timestamp()
today_end = today_start + 86400  # 24 hours in seconds

# Fetch records from today
cursor.execute("SELECT * FROM your_table WHERE timestamp_column BETWEEN ? AND ?", (today_start, today_end))

2. Can I compare a TEXT-stored date in SQLite with the current date from datetime? #

Absolutely! The only key requirement is that your TEXT dates are stored in a SQLite-recognizable format (like YYYY-MM-DD, YYYY-MM-DD HH:MM:SS, or YYYYMMDD). If they are, you can compare them either in SQL or Python:

Example 1: Compare directly in SQL #

If your TEXT column uses YYYY-MM-DD format, you can use SQLite's date functions to match directly:

cursor.execute("SELECT * FROM your_table WHERE text_date_column = DATE('now')")

Example 2: Compare in Python (for non-standard formats) #

If your TEXT dates are in a non-standard format (like DD/MM/YYYY), convert them to Python date objects first for comparison:

from datetime import datetime

cursor.execute("SELECT text_date_column FROM your_table")
all_dates = cursor.fetchall()
today = date.today()

for row in all_dates:
    # Parse the stored string into a date object (adjust the format string to match your storage)
    stored_date = datetime.strptime(row[0], "%d/%m/%Y").date()
    if stored_date == today:
        print("Matching record found:", row)

Just double-check that the format string in strptime exactly matches how your dates are stored in the TEXT column!

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