如何在MongoDB/pymongo中计算两个时间戳的分钟差值？

Great question! Let's map that MySQL query you have over to MongoDB, covering both database-side calculations (using aggregation) and app-side calculations in Python (since you mentioned datetime.datetime.now()).

Option 1: Database-Side Calculation with Aggregation #

If you want MongoDB to handle the calculation directly (just like your MySQL query runs on the database), you have two approaches depending on your MongoDB version:

For MongoDB 5.0+ (Using $dateDiff) #

MongoDB 5.0 introduced the $dateDiff operator, which makes this task super straightforward—it’s purpose-built for calculating date differences. Here’s how to use it with Python:

from pymongo import MongoClient
from datetime import datetime

# Connect to your database
client = MongoClient("your_connection_string")
db = client.your_database_name
collection = db.your_collection_name

# Aggregation pipeline to add the minute difference field
pipeline = [
    {
        "$addFields": {
            "minutes": {
                "$dateDiff": {
                    "startDate": "$lastseen",
                    "endDate": datetime.now(),
                    "unit": "minute"
                }
            }
        }
    }
]

# Run the query and get results
results = list(collection.aggregate(pipeline))

This adds a minutes field to each document, showing the rounded minute difference between lastseen and the current time—exactly matching the behavior of your MySQL query.

For Older MongoDB Versions (Pre-5.0) #

If you’re on a version before 5.0, you can use $subtract to get the millisecond difference between the two dates, then convert that to minutes and round it:

pipeline = [
    {
        "$addFields": {
            "minutes": {
                "$round": [
                    {
                        "$divide": [
                            {"$subtract": [datetime.now(), "$lastseen"]},
                            60 * 1000  # Convert milliseconds to minutes
                        ]
                    }
                ]
            }
        }
    }
]

results = list(collection.aggregate(pipeline))

$subtract on two MongoDB dates returns the difference in milliseconds, so dividing by 60 * 1000 converts it to minutes. The $round operator mimics MySQL’s ROUND() function to get a whole number.

Option 2: App-Side Calculation in Python #

Alternatively, you can fetch the documents first and calculate the difference directly in your Python code. This works well if you’re already processing documents in your app and want more flexibility:

from pymongo import MongoClient
from datetime import datetime

client = MongoClient("your_connection_string")
db = client.your_database_name
collection = db.your_collection_name

# Fetch relevant documents
documents = collection.find()

for doc in documents:
    lastseen = doc.get("lastseen")
    if lastseen:
        # Calculate time delta and convert to minutes
        time_delta = datetime.now() - lastseen
        minutes_since_lastseen = round(time_delta.total_seconds() / 60)
        doc["minutes"] = minutes_since_lastseen
    
    # Process the updated document (e.g., print, save back, etc.)
    print(doc)

This approach mirrors your MySQL query logic perfectly: get the total seconds between the two times, divide by 60, and round the result.

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