0

What is the proper way to convert a BsonTimestamp field to a C# DateTime type?

This is for data in MongoDB's oplog collection and using the MongoDB C# driver.

user990423
  • 1,397
  • 2
  • 12
  • 32
GaTechThomas
  • 5,421
  • 5
  • 43
  • 69
  • 1
    Looks like that can help:http://stackoverflow.com/questions/6036433/datetime-issues-with-mongo-and-c-sharp – Jester Oct 29 '15 at 20:09

3 Answers3

4

I believe the accepted answer in slightly off as Unix Epoch must be in UTC format.

var unixEpoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);

From BsonTimestamp to DateTime

var target = unixEpoch.AddSeconds(bsonTimestamp.Timestamp - 18000);

From DateTime to BsonTimestamp

var target = DateTime.UtcNow;
var diff = target.ToUniversalTime() - unixEpoch;
var seconds = (diff.TotalMilliseconds + 18000000) / 1000;
var ts = new BsonTimestamp((int)seconds, 1);

You need to use target.ToUniversalTime() to ensure the incoming parameter, if any, is always positioned for UTC.

Ostati
  • 4,623
  • 3
  • 44
  • 48
  • 1
    Great point! Fortunately our servers are on UTC, but this could lead to some local dev/test issues. – GaTechThomas May 24 '17 at 17:03
  • I think DateTime instance would be created correctly regardless of server configuration, you just have to indicate DateTimeKind.Utc. – Ostati May 26 '17 at 19:19
  • 1
    So the "18000" magic number is just the time zone (UTC-5) offset? – Vladislav Karamfilov Mar 06 '20 at 14:21
  • I think it is a "magic number", but I don't think it has anything to do with your timezone offset. In the above example when converting BsonTimestamp to DateTime, the value you get out of that is UTC. – JKH Oct 07 '21 at 14:20
  • It looks to me there is no reason for "18000". For me it works only if I omit it. – MarkusParker Nov 22 '22 at 15:19
2

MongoDB's Timestamp is the elapsed seconds since the Unix epoch (1970/1/1). Therefore, the conversion from Timestamp to DateTime is like this:

DateTime datetime = new DateTime(1970, 1, 1).AddSeconds(bsonTimestamp.Timestamp);

In terms of Value / Timestamp properties, they are implmented in both constructors of BsonTimestamp in https://github.com/mongodb/mongo-csharp-driver.

Constructor 1:

public BsonTimestamp(long value)
{
    _value = value;
}

Constructor 2:

public BsonTimestamp(int timestamp, int increment)
{
    _value = (long)(((ulong)(uint)timestamp << 32) | (ulong)(uint)increment);
}

Property:

public long Value
{
    get { return _value; }
}

public int Timestamp
{
    get { return (int)(_value >> 32); }
}

Since you are getting the timestamp records from oplog, their format would be like this:

Timestamp(1406171938, 1)

AS the second number (increment) is an ordinal number to make the Timestamp unique according to MongoDB reference, you should use Timestamp property I think.

jhmt
  • 1,401
  • 1
  • 11
  • 15
  • I worked up something similar but used bsonTimestamp.Timestamp instead of bsontTimestamp.Value. Do you know whether to prefer one over the other? If not, I can give it a spin tomorrow. – GaTechThomas Oct 29 '15 at 22:22
  • 1
    @GaTechThomas I mistook you are getting records from oplog then so I updated my answer. I think you might want to use `Timestamp` property. – jhmt Oct 30 '15 at 00:33
  • Fantastic info. Above and beyond. All answers should be written this way. – GaTechThomas Oct 30 '15 at 17:02
  • There's a great point added as a separate answer regarding time zone. It would be helpful to others if your answer were updated in that regard. Here's the other answer: https://stackoverflow.com/a/44140244/284598 – GaTechThomas May 24 '17 at 17:05
1

If you are using a BSON document:

DateTime dateTime = doc["BSONdateTime"].AsDateTime;

where "dateTime" is the variable you want to set, "doc" is the BSON document you extracted from MongoDB, and "BSONdateTime" is the key that you want to extract the date and time from.

I haven't tried this myself, but I was able to extract string values from BSON documents in MongoDB using:

string name = doc["name"].AsString;

I would also recommend you look into POCO, as this makes type conversion a lot easier and less boilerplate.

Hope this helps!

The Don
  • 343
  • 2
  • 13
  • 1
    From C# view I would recommend to use BsonDateTime.ToUniversalTime() or ToLocalTime(), depending on what you require. – Nick Oct 24 '19 at 09:44