Incorrect nanosecond values in Firestore date comparison queries

[szegadal]

Operating System

Windows 11

Environment (if applicable)

Firebase Admin SDK (Node.js backend), Firebase JavaScript SDK (Web frontend), Firebase Console UI (Firestore Database interface)

Firebase SDK Version

firebase-admin "^13.1.0

Firebase SDK Product(s)

Firestore

Project Tooling

Node.js V.22, Express, firebase-admin. But it actually affects Firebase Console UI, and any implementations in React with Firebase JavaScript SDK

Detailed Problem Description

Describe the bug

When using .where() queries with date comparison operators (>=, >, <, <=) in Firestore, the generated timestamp has incorrect millisecond values. Instead of using the correct timestamp format (e.g., 2025-10-10T00:00:00.000Z), the query generates timestamps with wrong millisecond precision, resulting in incorrect query results.

Affected environments:

✅ Firebase Admin SDK (Backend)
✅ Firebase Web SDK (Frontend)
✅ Firebase Console UI (Firestore query interface)

This appears to be a systemic issue affecting all Firebase/Firestore implementations.

Steps and code to reproduce issue

Example query:

// Frontend or Backend
db.collection('myCollection')
  .where('marca_de_tiempo', '>=', new Date('2025-10-01T00:00:00.000Z'))
  .get()

Expected behavior:

The query should generate a filter with correct timestamp:

{
  "fieldPath": "From",
  "op": "GREATER_THAN_OR_EQUAL",
  "value": {
    "timestampValue": "2025-10-01T04:00:00.000000000Z"
  }
}

Actual behavior:

The query generates incorrect millisecond values:

{
  "fieldPath": "From",
  "op": "GREATER_THAN_OR_EQUAL",
  "value": {
    "timestampValue": "2025-10-01T04:00:00.011000000Z"  // Wrong!
  }
}

Note the incorrect milliseconds: 011000000 instead of 000000000

Evidence

See attached screenshot showing the Network tab in Chrome DevTools where the structuredAggregationQuery request clearly shows the malformed timestamp in the compositeFilter section.

Key observations from screenshot:

• Input date: 1 oct 2025 at 00:00:00
• Expected nanoseconds: 000000000
• Actual nanoseconds: 011000000Z ❌

Impact

-Queries return incorrect results:

• Date range filters fail to work as expected
• Data consistency issues across the application
• Affects production systems using date-based queries

This affects specially reporting when data is aggregated in a hourly or daily basis.

Environment details

• Firebase Console: Cloud Firestore Database UI
• Browser: Chrome (DevTools visible in screenshot)
• SDKs affected: firebase-admin (Node.js backend) and Firebase Web SDK

Expected fix

Firestore queries should correctly serialize date/timestamp values in the where() clause with proper millisecond/nanosecond precision: 000000000 for midnight, or the exact value provided by the Date object.

[google-oss-bot]

I couldn't figure out how to label this issue, so I've labeled it for a human to triage. Hang tight.

[hsubox76]

I wasn't able to reproduce this. I tried using your simple example and didn't see it, and then since your screenshot shows a more complex example (two sums and two where conditions) I tried to copy that as well.

const coll = collection(firestore, "items");
const q = query(coll,
  where("time", "<=", new Date("2025-10-31T00:00:00.000Z")),
  where("time", ">", new Date("2025-10-01T00:00:00.000Z"))
);
const snapshot = await getAggregateFromServer(q, {
  total: sum("field2"),
  total2: sum("field2"),
});

console.log(snapshot.data())

The result seems to match exactly:

I'm using Chrome on MacOS. Do you think there's anything else needed to reproduce this? Do you see this every time or only sometimes?