Working with datetime as string

Working with Data
1

Let’s say some external component inserts data into my database, with a field called “startDate”. They store this value as an ISO 8601 formatted string (“2020-07-29T07:55:55.485Z”).

Can I still run date time queries on it, even though it’s not stored as an ISODate object or any other structured class? Would mongodb be smart enough to treat them as such? Or anything I can do to improve my situation?

2 Likes
2

Hello @Nathan_Hazout,

Can I still run date time queries on it, even though it’s not stored as an ISODate object or any other structured class?

Yes. You can still run queries on the date field.

Would mongodb be smart enough to treat them as such?

MongoDB will treat them as they are - string data type. And, the string comparison rules will apply. You can safely store dates as strings and query on them as long as they are properly formatted for date, i.e., “YYYY-MM-ddTHH:mm:ss”.

Or anything I can do to improve my situation?

Your situation is not bad, I think.

Suppose you have a documents like this:

{ _id: 1, date: "2020-07-25T14:10:26.113Z" }
{ _id: 2, date: "2020-07-29T07:55:55.485Z" }
{ _id: 9, date: "2020-08-01T01:00:12.002Z" }

An example query to find documents which have date greater than the given date (also of type string):

db.test.find( { date: { $gt: "2020-07-25" } } )

The above query fetches all the documents. Then, you can also query like this:

db.dates.find( { date: { $gt: "2020-07-25", $lte: "2020-07-31" } } )

This is also the case with the aggregation queries. And, in case you need to match using date objects, there are aggregation date operators to convert to specific format (string to date object or vice-versa).

That said, please do tell about any specific type of queries you have in your application.

1 Like
3

Thanks @Prasad_Saya, I thought about sorting as string, but you are making assumptions here.

As I said, I don’t control the input of those dates, all I know is ISO 8601. Which means, they might have different timezones, in which case if I understand correctly the string sort would no longer hold (2020-07-29T10:53:43+00:00 and 2020-07-29T08:53:43-02:00 represent the same timestamp yet are different strings)

Also, there are all kinds of ISO 8601 theoretically, all of those are valid and equal per ISO 8601:

2020-07-29T10:53:43+00:00
2020-07-29T10:53:43Z
2020-07-29T10:53:43.00000Z
20200729T105343Z
4

Hello @Nathan_Hazout.

As long as all the string date values are in ISO 8601 standard format, you can convert them into the date object and perform your sort operation.

For example, use the Aggregation date operator $toDate will convert the string dates in ISO 8601 format to date objects:

Input documents:

{ "_id" : 1, "date" : "2020-07-29T10:53:48+00:00" }
{ "_id" : 2, "date" : "2020-06-11T10:53:43Z" }
{ "_id" : 3, "date" : "2020-01-09T21:53:43.00000Z" }
{ "_id" : 4, "date" : "20200729T105345Z" }

This query with sort on the date:

db.test.aggregate([
  { $addFields: { date: { $toDate: "$date" } } }, 
  { $sort: { date: 1 } }
])

The sorted result:

{ "_id" : 3, "date" : ISODate("2020-01-09T21:53:43Z") }
{ "_id" : 2, "date" : ISODate("2020-06-11T10:53:43Z") }
{ "_id" : 4, "date" : ISODate("2020-07-29T10:53:45Z") }
{ "_id" : 1, "date" : ISODate("2020-07-29T10:53:48Z") }
5

Yes I imagined something of this sort. Surely however, I would loose the benefit of any indexes on these fields right?

6

Yes, the index on the original string date field will not apply on the derived field.

7

Here is a thought, while I don’t control the INPUT from the users (hence the slight variability in date formats), I control the database itself. Is there a way to configure the database to convert the strings to dates “on write”? Therefore giving me the power of indexes while leaving the flexibility to the user?

Again assuming the strings are always valid ISO 8601.

8

Change Streams allow applications to access real-time data changes without the complexity and risk of tailing the oplog. Applications can use change streams to subscribe to all data changes on a single collection, a database, or an entire deployment, and immediately react to them. Because change streams use the aggregation framework, applications can also filter for specific changes or transform the notifications at will.

11

Just to add about storage space taken up by Date vs String.

Got it from this link: json - MongoDB: should I use string instead of date? - Stack Overflow

  • A BSON Date is just 8 byte.
  • A date in the minimal form of YYYYMMDD is 12 byte (strings in BSON are prefixed with a 4 byte integer for the length).
  • When you store it as an ISODate string which uses all the ISO 8601 standard has to offer (date, time accurate to millisecond and timezone), you have 32 byte - four times the storage space.
1 Like