如何在MongoDB中获取类似SQL里SYSTIMESTAMP的系统时间？

If you’re familiar with Oracle’s SYSTIMESTAMP (which returns the database server’s current timestamp including timezone offset) and want the same functionality in MongoDB, here’s how you can get system-level timestamps with timezone information:

1. Fetch MongoDB Server's Current Timestamp (Mirroring SELECT SYSTIMESTAMP FROM DUAL) #

To get the server’s current time (not your client’s local time) in a query, use the $currentDate operator in an aggregation pipeline. This matches Oracle’s behavior of returning the database server’s system time.

For a timezone-aware date object: #

db.aggregate([
  {
    $project: {
      serverTimestamp: { $currentDate: { type: "date" } },
      _id: 0
    }
  }
])

You’ll get a result like:

{ "serverTimestamp" : ISODate("2024-05-20T12:45:30.123Z") }

The ISODate format uses UTC (Z), and your shell/driver will automatically convert it to your local timezone for display if configured.

For high-precision nanosecond timestamps (matching Oracle's SYSTIMESTAMP precision): #

If you need fractional seconds up to nanoseconds (just like Oracle’s SYSTIMESTAMP), use the timestamp type:

db.aggregate([
  {
    $project: {
      serverTimestamp: { $currentDate: { type: "timestamp" } },
      _id: 0
    }
  }
])

Example result:

{ "serverTimestamp" : Timestamp(1716213930, 123456) }

The first value is the Unix epoch in seconds, the second is the nanosecond fraction.

2. Get Client-Side Current Timestamp #

If you only need your local client’s current time (not the server’s), directly use the Date constructor in the MongoDB Shell:

new Date()
// Shorthand alternative:
ISODate()

This returns an ISODate object, and converting it to a string will show your local timezone offset:

new Date().toString()
// Output example: "Mon May 20 2024 20:45:30 GMT+0800 (China Standard Time)"

3. Auto-Set Timestamps in Document Writes #

To automatically set a timestamp field when inserting or updating documents (like using SYSTIMESTAMP as a default value in Oracle), use $currentDate in your write operations:

// Insert a document with a server-side creation timestamp
db.yourCollection.insertOne({
  data: "sample content",
  createdAt: { $currentDate: { type: "date" } }
})

// Update a document to set a server-side last-modified timestamp
db.yourCollection.updateOne(
  { _id: ObjectId("your-document-id") },
  { $set: { data: "updated content" }, $currentDate: { lastModified: true } }
)

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