[csharp] Advice for using Dates in MongoDB

[Bryan Migliorisi]

Hi

Some of these questions apply specifically to the C# driver but some
are more about MongoDB in general.

I am building an analytics system. I have my data structure all sorted
out and it records and queries beautifully except for one thing - the
dates. I understand that dates in Mongo are stored in UTC and
represented as 64 bit integers, but that is about all I know of them.

1) If I insert a document with a date, is it the job of the driver or
the server to convert it to a UTC 64-bit integer? I imagine that its
the driver since there is no JSON representation of a date.

2) Is it best if the server running mongod has its system date\time
set to UTC or does it not matter? What about the application?

3) When I create my date object, does it matter if I create it as a
UTC DateTime object or not, since it always gets stored as UTC anyway?

4) Is it best to work with ONLY UTC DateTime objects or can LocalTime
DateTime objects suffice?

Thanks
Bryan Migliorisi

[Robert Stam]

DateTimes and time zones are always fun...

The server does not do any conversion to UTC, it just stores whatever
value the client sends to it.

The C# driver converts all DateTimes to UTC before sending them to the
server. Whether it converts back to LocalTime or not when retrieving
the data is more complicated.

When using BsonDocuments:

A DateTime value passed to a BsonDateTime constructor is stored as-is
in the BsonDateTime in-memory object, it is not yet converted to UTC.
So if you call BsonDateTime.Value you will get back exactly the same
DateTime value you passed to the constructor. The conversion to UTC
happens when the value is sent to the server. When a BsonDateTime is
read back from the server it will always be in UTC.

When using POCOs:

You can provide some control over how DateTimes are serialized and
deserialized using this attribute:

[BsonDateTimeOptions(DateOnly = true|false, Kind = DateTimeKind.Utc|
Local|Unspecified, Representation = BsonType.DateTime|Document|Int64|
String)]

When DateOnly = true the driver enforces that the TimeOfDay component
be zero, and also doesn't convert to UTC so that the TimeOfDay
component will be zero on the server also.

When Kind = Utc, values returned from the server remain in UTC. When
Kind = Local or Unspecified, values returned from the server are
converted to LocalTime and the Kind property is set to Local or
Unspecified.

For representation stick to BsonType.DateTime, *unless* you need to
store .NET DateTimes *without* losing precision (the BSON DateTime
representation is only accurate to the millisecond). Document
representation stores the value twice, once as a BSON value (so you
can write queries against easily) and once as a 64 bit .NET Ticks
value. Int64 stores just the .NET Ticks value, and String stores the
DateTime as an ISO-8601 string.

My recommendations:

- always run servers set to the GMT time zone (not everyone does this)
- always use UTC in your data models and only convert to LocalTime
when displaying to the user

Also, keep in mind that "LocalTime" is based on the time zone setting
of the machine your code is running on. This is often the wrong
timezone anyway. Your code may be running on a server set to GMT. Or
your application may be used by users in multiple time zones and you
need to be multi time zone aware anyway.

[Bryan Migliorisi]

Incredibly helpful as always Robert.  

This totally clears up everything for me.  I did not know about the serialization options for the DateTime class.

Im sure the last thing you want to do is write documentation but is there any place that lists and describes the different serialization attributes?  Ive looked through the serialization tutorial but that doesnt have too much, and it does not seem to have been updated since it was written.

Thanks again,

Bryan Migliorisi

[Robert Stam]

I am in the process of updating the serialization tutorial:

http://www.mongodb.org/display/DOCS/CSharp+Driver+Serialization+Tutorial

to include the new serialization features added in version 1.1.

You're right, it had gotten a bit out of date...

[Robert Stam]

The C# Driver serialization tutorial has been updated.

New topics:

- New IsClassMapRegistered method
- Selecting an IdGenerator
- NullIdChecker and ZeroIdChecker (and how to enable them)
- ShouldSerializeXyz methods
- Identifying required fields
- string Id represented externally as an ObjectId
- heading for BsonDateTimeOptions section now actually has the word
"DateTime" in it...

[Bryan Migliorisi]

Somebody give this guy a raise ;)

Thanks again Robert!

--Bryan

[Bryan Migliorisi]

Robert,

I have a DateTime object and I want to store it as a string.  I thought the following code would accomplish this, but it still gets stored\represented in the database as an ISODate:

[BsonDateTimeOptions(Representation = BsonType.String, Kind = DateTimeKind.Utc)]
public DateTime Start { get; set; }

In the shell, it looks like this:

"s" : ISODate("2011-06-22T22:19:00Z")

My goal is to store the DateTime object as a string in the database such that I can perform regular expression matching on the date string.  However, when pulling data out of the database, I want to have that string represented as a DateTime object.  Precision is not important in this case as this is more for logging and stats.

Suggestions?

Thanks,
Bryan

[Robert Stam]

Are you sure you're looking at the same document in the shell? For
one, your element names are different.

It works fine for me. Using this sample program:

http://www.pastie.org/2185781

Resulted in this document being inserted:

> db.test.find()
{ "_id" : ObjectId("4e17aae6e447adb9e471a289"), "Start" :
"2011-07-09T01:11:53.7759616Z" }
>

Where Start is a string like it is supposed to be.

[Bryan Migliorisi]

Hi Robert,

I was looking at the correct document, I left out the [BsonElement("s")]  line.

Anyway, everything works fine - I overlooked the fact that after inserting the new document, my app was updating the document and the update was performing a $set on that field and setting it to a DateTime object.

Sorry for wasting your time.

--Bryan

[Robert Stam]

No problem. Glad you figured out what was going on!