在 MongoDB 中存储日期的最佳方法是什么？

What is the best way to store dates in MongoDB?

提问by burger

I am just starting to learn about MongoDB and hoping to slowly migrate from MySQL.

刚开始学习MongoDB，希望能慢慢从MySQL迁移过来。

In MySQL, there are two different data types - DATE ('0000-00-00')and DATETIME ('0000-00-00 00:00:00'). In my MySQL, I use the DATEtype, but I am not sure how to transfer them into MongoDB. In MongoDB, there is a Dateobject, which is comparable to DATETIME. It seems it would be most appropriate to use Dateobjects, but that would be wasting space, since hours, min, sec are not utilized. On the other hand, storing dates as strings seems wrong.

在 MySQL 中，有两种不同的数据类型 -DATE ('0000-00-00')和DATETIME ('0000-00-00 00:00:00'). 在我的 MySQL 中，我使用了该DATE类型，但我不确定如何将它们传输到 MongoDB。在 MongoDB 中，有一个Date对象，它相当于DATETIME. 使用Date对象似乎是最合适的，但这会浪费空间，因为没有使用小时、分钟、秒。另一方面，将日期存储为字符串似乎是错误的。

Is there a golden standard on storing dates ('0000-00-00')in MongoDB?

('0000-00-00')在 MongoDB 中存储日期是否有黄金标准？

采纳答案by Remon van Vliet

BSON (the storage data format used by mongo natively) has a dedicated date type UTC datetimewhich is a 64 bit (so, 8 byte) signed integer denoting milliseconds since Unix time epoch. There are very few valid reasons why you would use any other type for storing dates and timestamps.

BSON（mongo 本地使用的存储数据格式）有一个专用的日期类型 UTC 日期时间，它是一个 64 位（因此，8 字节）有符号整数，表示自 Unix 时间纪元以来的毫秒数。使用任何其他类型来存储日期和时间戳的正当理由很少。

If you're desperate to save a few bytes per date (again, with mongo's padding and minimum block size and everything this is only worth the trouble in very rare cases) you can store dates as a 3 byte binary blob by storing it as an unsigned integer in YYYYMMDD format, or a 2 byte binary blob denoting "days since January 1st of year X" where X must be chosen appropriately since that only supports a date range spanning 179 years.

如果您迫切希望每个日期节省几个字节（同样，使用 mongo 的填充和最小块大小以及所有这一切仅在极少数情况下才值得麻烦），您可以将日期存储为 3 字节二进制 blob，方法是将其存储为unsigned integer in YYYYMMDD format, or a 2 byte binary blob denoting "days since January 1st of year X" where X must be chosen appropriately since that only supports a date range spanning 179 years.

EDIT: As the discussion below demonstrates this is only a viable approach in very rare circumstances. Basically; use mongo's native date type ;)

编辑：正如下面的讨论所表明的，这只是在极少数情况下可行的方法。基本上; 使用 mongo 的原生日期类型；)

回答by Geoffrey Booth

I'm actually in the process of converting a MongoDB database where dates are stored as proper Date() types to instead store them as strings in the form yyyy-mm-dd. Why, considering that every other answerer says that this is a horrible idea? Simply put, because of the neverending pain I've been suffering trying to work with dates in JavaScript, which has no (real) concept of timezones. I had been storing UTC dates in MongoDB, i.e. a Date() object with my desired date and the time set as midnight UTC, but it's unexpectedly complicated and error-prone to get a user-submitted date correctly converted to that from whatever timezone they happen to be in. I've been struggling to get my JavaScript "whatever local timezone to UTC" code to work (and yes, I'm aware of Sugar.js and Moment.js) and I've decided that simple strings like the good old MySQL standard yyyy-mm-ddis the way to go, and I'll parse into Date() objects as needed at runtime on the client side.

我实际上正在转换 MongoDB 数据库，其中日期存储为正确的 Date() 类型，而不是将它们存储为表单中的字符串 yyyy-mm-dd. 为什么，考虑到每个其他回答者都说这是一个可怕的想法？简而言之，因为我一直在尝试在 JavaScript 中处理日期而遭受无休止的痛苦，而 JavaScript 没有（真正的）时区概念。我一直在 MongoDB 中存储 UTC 日期，即一个 Date() 对象，我想要的日期和时间设置为 UTC 午夜，但是将用户提交的日期从他们的任何时区正确转换为该日期出乎意料地复杂且容易出错碰巧在。我一直在努力让我的 JavaScript“任何本地时区到 UTC”代码工作（是的，我知道 Sugar.js 和 Moment.js），我已经决定像这样的简单字符串好的旧 MySQL 标准yyyy-mm-dd是要走的路，我将在客户端运行时根据需要解析为 Date() 对象。

Incidentally, I'm also trying to sync this MongoDB database with a FileMaker database, which also has no concept of timezones. For me the simplicity of simply notstoring time data, especially when it's meaningless like UTC midnight, helps ensure less-buggy code even if I have to parse to and from the string dates now and then.

顺便说一句，我还尝试将此 MongoDB 数据库与 FileMaker 数据库同步，该数据库也没有时区概念。对我来说，简单地不存储时间数据的简单性，尤其是当它像 UTC 午夜一样毫无意义时，有助于确保代码错误更少，即使我必须时不时地解析字符串日期。

回答by pingw33n

If you really care about saving 4 bytes per field (in case you have many DATEfields per document) you can store dates as int32fields in form 20110720(note MySQL DATEoccupies 3 bytes, so the storage will be greater in any case). Otherwise I'd better stick to standard datetime type.

如果您真的关心每个字段节省 4 个字节（如果DATE每个文档有多个字段），您可以将日期存储为int32表单中的字段20110720（注意 MySQLDATE占用 3 个字节，因此在任何情况下存储都会更大）。否则我最好坚持标准日期时间类型。