Date/Timestamp comparison doesn't work reliably in query

Hi, I am trying a query to find out whether new changes after a timestamp. However, the result is sometimes correct while wrong other times.
Will you please check anything missed? Really haven’t dealt much with the date/time and different formats in MongoDB.

Here are the related code:
src_last_change_time = self.get_src_last_change_time(collection_name)
`
convert BSON Timestamp to ISO Date
looks like the compare using the ISODate string which has “Z” between date and time and so the result
wrong with above src_last_change_date has " " between date and time, and so only date part works
Fix by convert to iso format

src_last_change_date = datetime.fromtimestamp(src_last_change_time.time,
tz=timezone.utc).isoformat()

change_collection = self.db[collection_name]

count = change_collection.count_documents(
{“$or”: [{‘first_deployment_date’: {‘$exists’: True, ‘$gt’: src_last_change_date}},
{‘document_last_change_date’: {‘$exists’: True, ‘$gt’: src_last_change_date}}]}, limit=1)

    return count > 0

`

Thank you very much!

Mary

To make the question more clear, here are the steps and context:
I am trying to check which collections have changes to retry by timer after a recent failure case to prevent unnecessary wait. Somehow the comparison using the clusterTime from previous change to record change dates doesn’t work well. Before I add the isoformat() conversion, different dates work fine while same date with only difference in time failed and I found " " instead of “Z” in the converted clusterTime and so I added isoformat() to get “Z” added in, it fixes the issue of same date with only time difference. However, looks like different dates stopped working which is very strange since the convert string format and the DB query of 2 date fields to be compared all look correct with “T” though I don’t see the “Z” in the end of the string.

  • Get last change timestamp

Get the clusterTime from latest MongoDB change stream event which is Timestamp or BSON Timestamp format
src_last_change_time = self.get_src_last_change_time(collection_name)

  • Convert Timestamp to ISO Date
    src_last_change_date = datetime.fromtimestamp(src_last_change_time.time, tz=timezone.utc).isoformat()

  • Check any changes in the collection after above ISO date
    `change_collection = self.db[collection_name]

count = change_collection.count_documents(
{“$or”: [{‘first_deployment_date’: {‘$exists’: True, ‘$gt’: src_last_change_date}},
{‘document_last_change_date’: {‘$exists’: True, ‘$gt’: src_last_change_date}}]}, limit=1)
return count > 0
`

Not sure I have missed anything basic, but it really feels like strange.

Thank you!

Some example:

  1. dates in the record from query:
    "first_deployment_date" : ISODate("2024-01-27T11:48:10.602+0000"), "document_last_change_date" : ISODate("2024-02-28T16:48:10.602+0000")
  2. last change Timestamp:
    'clusterTime': Timestamp(1706334412, 1)
    it’s converted to ‘2024-01-27T05:46:52+00:00’
    However, the query doesn’t detect the changes.

Looks like if I change the query to compare only one date field, it works fine.
And so maybe the query syntax for $or has issue though it worked fine from my previous tests for different dates only w/o the isoformat() added?
Very strange!

Hello,

The following docs might have some help with respect to this context.

Especially there is a mention about the Z part in date time format which seems the main issue in this case.

Thanks
WeDoTheBest4You

1 Like

Thank you.
Sorry that I forgot to update here with another similar post updated.

There are 2 issues I found out:

  1. The isoformat() will return a string format which cannot be used in the query to compare with ISODate in the collection record directly. Somehow the query doesn’t return any error but always 0 which delayed the finding of the cause.

  2. The clusterTime or BSON Timestamp from change stream doesn’t have the milliseconds part and so using it to check any new changes may not always work which made me try isoformat() in 1 since I found the log of the converted date doesn’t have “Z” between date and time but a space instead and isoformat() makes the log looking correct with “Z”.

So what I need is the milliseconds part to be included in the change stream to have reliable timestamp to compare.

Thank you!

Mary