2

In MongoDB, I only need to make date range queries. But the data set is huge (9 M) and coverting a string to DateTime object (I use Perl script) and then inserting them into MongoDB is very time consuming. If I just store the dates as strings "YYYY-MM-DD", would not the range query gt:"2013-06-01" and lt:"2013-08-31" still give me the same results as if they were of datetime type? Are they the same in this scenario? If so, what would be the advantage of storing as a DateTime object. Thanks.

Dan Dascalescu
  • 143,271
  • 52
  • 317
  • 404
Sai
  • 461
  • 7
  • 25
  • [Perl in particular is slow at creating DateTime objects](http://stackoverflow.com/questions/4973649/storing-datetime-in-mongodb-effects-on-performance). See also [the most versatile way of storing dates in MongoDB](http://stackoverflow.com/questions/3778428/best-way-to-store-date-time-in-mongodb) – Dan Dascalescu Feb 10 '14 at 11:02

2 Answers2

5

If you don't care about time-zone support in your application, then using strings for basic queries in MongoDB should work fine (but if it does matter, you'll want a real Date type).

However, if you later want to do date math or use the Aggregation Framework with your date field, it's necessary that the field is actually a Date type:

http://docs.mongodb.org/manual/reference/aggregation/#date-operators

For example, you could use the $dayOfWeek function on the Date typed field.

You could likely do some simple things like group on year by using $substr (doc) in MongoDB, but the resulting code will not be as clear (nor likely perform as well).

While it's not a huge difference, I'd recommend storing them as Date types if possible generally.

I see in the docs for the Perl driver that developers are warned against using the DateTime due to the fact that it is very slow, so maybe if you use Perl regularly, and the Aggregation Framework isn't a big issue, you'd be better off storing them as either numbers or as strings, and converting them as needed in Perl.

If space is an issue, remove unnecessary characters (such as the -):

20130613 -> 
    4 bytes for length of string 
    8 bytes encoded as UTF-8
    NULL character

That would be 13 characters. A DateTime value in BSON/MongoDB requires 8 bytes on the other hand (as would the Perl $time function).

(I'd strongly recommend you do a bit of performance testing to find out if the performance impact of using a Date type in MongoDB with Perl will impact your typical workflows.)

WiredPrairie
  • 58,954
  • 17
  • 116
  • 143
  • Very well put answer. Thank you very much. It makes sense to remove the extra characters from the date. While the aggregation operator is a very useful feature for small datasets, it regularly runs out of RAM in larger datasets and hence in the long run, maybe storing as strings would be the wiser option. – Sai Jun 14 '13 at 15:32
  • 1
    (I'm a little surprised you accepted the other answer, especially as it contains at least one mistake and very little detail, but I'll get over it. :) ). – WiredPrairie Jun 14 '13 at 15:42
  • Actually I did accept your answer. Just that I was new to Stackoverflow and thought that I could select both as answers ( very Noob-ish indeed ). I realized now that only one can be selected, so there you go. – Sai Jun 14 '13 at 15:49
  • 1
    If you ever test the Perl performance of the DateTime and MongoDB, you should post your results somewhere. – WiredPrairie Jun 14 '13 at 15:50
  • I have currently tested it out on three different datasets, on the really small ones (few thousand records long) it really doesn't make a difference. But on the really large ones (>5 M records long), there was significant reduction in running time from 25 m to 5 m. more detailed comparisons shall be posted. – Sai Jun 14 '13 at 15:53
3

The advantage of DateTime is a few bytes less on disk. bson stores DateTime as an integer, but "2013-08-31" is a string, at 20 bytes right there.

ISO-8601 (http://www.w3.org/QA/Tips/iso-date) is meant for being able to sort quickly.

In this case, I would always store as datetime.

edit: How time-consuming are you seeing this string-to-datetime conversion? Are you sure that is your bottleneck? I have a hard time believing the conversion is taking as long as you claim.

000
  • 26,951
  • 10
  • 71
  • 101
  • Ok that's understandable that it takes up less space on disk. Thanks for the pointers. I'll just use them as strings then. – Sai Jun 13 '13 at 23:20
  • 1
    Well the data set is such that without the conversion I could do a quick (10 mins) and dirty mongo-import into the DB. However if I have to change to DateTime type, either using Perl script or using the Mongo command line, it takes twice as much time (17-18 mins to be precise). – Sai Jun 13 '13 at 23:24
  • 1
    Probably worth it in the long run. – 000 Jun 13 '13 at 23:25