0

I see people storing / getting the server time and times relative to it using date or getTime which can be kept in the database as a string of the sorts: "July 21, 1983 01:15:00".

Up until now I stored my server time as the difference between NOW and 1 january 2013. This would return a number value (in minutes), rounded down between 1 jan 2013 and right now, which I keep as internal server time.

The advantages of this are that: - querying the server implies a simple numeric comparison operation, while (I make an educated guess) comparing two dates implying internal conversion to objects and using fat comparison operations. - storing a number of that size is more lightweight than a string of ~25 characters. - converting back to "real" time is by adding 1 jan 2013 but second and millisecond values are lost due to initial roundness.

But still, other fellow programmers insist that using the string version - is easy to read as a human. - its an universal format for most languages (especially nodejs, mongodb and as3 which this project has).

I am uncertain which is better for large scale databases and specifically, for a multiplayer socket based game. I am sure others with real experience in this could shed some light on my issue.

So which is better and why?

Discipol
  • 3,137
  • 4
  • 22
  • 41
  • 1
    Store them as UTC as well and make your app tz aware, that way you have true internationalisation. I would really argue with the programmers you have been talking to...the database stores data it is not supposed to be human readable that is why you make applications – Sammaye Aug 22 '13 at 10:58
  • So why not store them as difference between 1 jan 2013? This way both dates are in the same timezone. In fact all differences are between same timezone dates. – Discipol Aug 22 '13 at 11:11
  • @Discipol If you are storing them as int and they will be future proof there is nothing against that part, any other date that neds greater resolution should be datetime object though, string dates are a death knell, so many systems I have taken over that have made those mistakes... – Sammaye Aug 22 '13 at 11:24
  • I may have expressed myself wrongly, with a mix of ignorance. I am storing in the server as mongodb date of course :| I just thought the difference version would take up less pace / be faster in comparisons :| I could not understand if my difference idea is futureproof/ efficient tho, you seem to have handled your share of large servers. – Discipol Aug 22 '13 at 11:32
  • Oh ok, yes the difference verison would take up less space. I should have made myself clearer as well, the future proofing is fine on the database end, you need to make sure that in your app you will never suffer because of this. One time you may suffer is when your application is years, maybe decades old and you need to do advanced date aggregation functions, this is where a full date object could be more beneficial – Sammaye Aug 22 '13 at 13:04
  • You have an extremely valid point, 3 years in the future would increase that difference value quite a bit. Thing is, my game resets every 3 months, as in I archive the database and wipe it clean, at which point I set the starting date to the launch day. Its a Travian-like game. – Discipol Aug 22 '13 at 13:07
  • Hmm, to be honest I don't see much a problem, you will never have a date range great enough to merit the use of complicated aggregation framework functions on them (the aggregation framework can only use date objects) – Sammaye Aug 22 '13 at 13:20

1 Answers1

1

Store them as Mongo Date objects. Mongo stores dates as 8-byte second-offset integers [1], and displays them in human readable format. You are NOT storing 25 characters!

Therefore, all comparisons are just as fast. There is no string parsing except for when you're querying, which is a one-time operation per query.

Your difference is stored as either as an int of 4 bytes. So you're saving ONLY 4 bytes over normal MongoDB date storage. That's a very small savings, considering against the average size of your mongo objects.

Consider all the disadvantages of your "offset since January 2013" method:

  • Time spent writing extra logic to offset the dates when updating or querying.
  • Time spent dealing with bugs that arise from having forgotten to offset a date.
  • Time spent shifting dates by hand or in your head when inspecting database output (when diagnosing a problem), instead of seeing the actual date right away.
  • Inability to use date operators in the MongoDB aggregations without extra work (e.g. $dayOfMonth, extra work being a projection to shift your dates internally to ).

Basically, more code and more headache and more time spent, all to save 4 bytes on objects in a database where the same 4 bytes can be saved by renaming your field from "updated" to "upd"? I don't think that's a wise tradeoff.

Also, Best way to store date/time in mongodb

Premature optimization is the root of all evil. Don't optimize unless you've determined something to be a problem.

1 - http://bsonspec.org/#/specification

Community
  • 1
  • 1
Astral
  • 954
  • 1
  • 7
  • 9