0

Okay, I'm a SQL Server based DBA, but there's a biz-critical app that uses MongoDB as a core component, and the problem is that it's grown too large. (The "large-ness" isn't critical yet, I'm trying to be proactive!)

Specifically, it has a "message log" collection which is over 400 GB, where the date-stamp is actually stored as a 2-element array [Int64, Int32], the 0th element being some measure of time (and the 1th element is just always '0').

So for example, a document:

{
    "_id" : ObjectId("55ef63618c782e0afcf346cf"),
    "CertNumber" : null,
    "MachineName" : "WORKERBEE1",
    "DateTime" : [ 
        NumberLong(635773487051900000), 
        0
    ],
    "Message" : "Waited 00:00:30.0013381 to get queue lock: Specs to verify",
    "ScopeStart" : false
}

And just because 2 is better than 1, another example document:

{
    "_id" : ObjectId("55ef63618c782e0afcf323be"),
    "CertNumber" : null,
    "MachineName" : "WORKERBEE2",
    "DateTime" : [ 
        NumberLong(635773487056430453), 
        0
    ],
    "Message" : "Waited 00:00:30.0012345 to get queue lock: Specs to verify",
    "ScopeStart" : false
}

I need to figure out two things:

  1. What the heck does that "DateTime" really mean? It's not Unix Epoch time (seconds nor milliseconds); and even if I strip off the trailing 0's, it represents (in millis) 6/20/2171, so, unless we're building a time machine here, it makes no sense. If I strip off the last 6 digits, it means 2/23/1990, but even that doesn't seem likely, as this application has only existed since the early 2000's. (AFAIK)

  2. Assuming we figure out #1, can we use some kind of command to remove (delete) all documents in the collection that are older than, say, 1/1/2016?

Again, I'm a SQL guy, so try to explain using analogs in that vein, e.g. "this is like your WHERE clause" and such.

PS: Yes, I read thru questions such as Find objects between two dates MongoDB and How do I convert a property in MongoDB from text to date type? , but so far nothing has jumped out at me.

Community
  • 1
  • 1
NateJ
  • 1,935
  • 1
  • 25
  • 34
  • Answer to Q1: http://stackoverflow.com/questions/22964199/how-to-convert-numberlong-to-date-in-mongodbs-shell – Barney Jul 15 '16 at 00:53
  • Nice tip; still not applicable. As I said, the number actually makes no sense as a standalone date/time, even in milliseconds. – NateJ Jul 19 '16 at 18:02
  • Okay, got an answer finally for #1: the array is basically a "raw" representation of the C# `DateTimeOffset` datatype. [0] element represents "ticks" - 100-nanosecond intervals since 12 midnight on 1/1/0001. [1] element is the timezone offset ("0" in this case since it's UTC). That's why it makes no sense to Unix/Epoch time converters! – NateJ Jul 21 '16 at 20:35
  • The "friendly" interpretation of those 2 sample datetimeoffset values is 9/8/2015 10:38:25 PM give or take some milliseconds. The full answer will involve creating an index in the mongo collection and attempting to delete documents by referencing the field. TBD... – NateJ Jul 23 '16 at 00:01

0 Answers0