MongoDB中存储为时间戳的日期范围查询方法

Hey there! I get it, dealing with timestamp-based date ranges can be tricky when most guides focus on ISO dates—let's break this down step by step so you can get your queries working.

First, let's clarify a key point: MongoDB has two common "timestamp" types you might be using, so we'll cover both scenarios.

1. If your time field is a Unix Timestamp (numeric: seconds or milliseconds) #

This is the most common case—your time stores a number like 1704067200 (seconds since epoch) or 1704067200000 (milliseconds).

The core approach: #

Convert your target start/end dates into matching timestamp values, then use MongoDB's comparison operators ($gte, $lt) to filter documents.

Example: Query documents from 2024-01-01 to 2024-01-02 (inclusive of 1st, exclusive of 2nd)

First, get the timestamp for your date range:

• For milliseconds:
  • Start: 2024-01-01 00:00:00 UTC → 1704067200000
  • End: 2024-01-02 00:00:00 UTC → 1704153600000
• For seconds:
  • Start: 1704067200
  • End: 1704153600

Then run this query (replace yourCollection with your actual collection name):

// For millisecond timestamps
db.yourCollection.find({
  time: {
    $gte: 1704067200000,
    $lt: 1704153600000
  }
})

// For second timestamps
db.yourCollection.find({
  time: {
    $gte: 1704067200,
    $lt: 1704153600
  }
})

Why $lt instead of $lte? It ensures you don't include documents from the start of the next day—adjust to $lte if you want to include the exact end time.

Pro tip to get timestamps quickly: #

In the MongoDB shell, you can generate timestamps on the fly using ISODate:

// Get millisecond timestamp for a date
ISODate("2024-01-01T00:00:00Z").getTime()

// Get second timestamp (divide by 1000 and floor)
Math.floor(ISODate("2024-01-01T00:00:00Z").getTime() / 1000)

The Z at the end ensures you're using UTC time—critical if your timestamps are stored in UTC (which they usually should be!).

2. If your time field is MongoDB's native Timestamp type #

MongoDB has a special Timestamp BSON type (different from Date), which looks like Timestamp(1704067200, 0) where the first value is a second-based timestamp, and the second is an incrementing ordinal.

For this type, you'll need to construct Timestamp objects in your query:

db.yourCollection.find({
  time: {
    $gte: Timestamp(1704067200, 0),
    $lt: Timestamp(1704153600, 0)
  }
})

Common reasons your query might not be working: #

• Wrong timestamp unit: Mixing seconds and milliseconds is the #1 culprit. Double-check if your time field stores seconds or milliseconds.
• Timezone mismatch: If your timestamps are in UTC but you converted your dates using local time, your range will be off. Always use UTC dates when generating timestamps.
• Typos: Double-check your collection name, field name (time), and operator syntax ($gte not gte).

Give this a shot, and if you're still having trouble, you can run db.yourCollection.findOne() to inspect a sample document and confirm what type your time field is—this will help narrow things down!

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