Charts don't work with saved String date

[Daniel_Nordh]

I’m struggling to create Charts with data that have date information.

Our data is saved as String type, for example:
“Tue Jan 10 2023 12:33:40 GMT+0000 (Coordinated Universal Time)”

If I try to use this directly in a Chart, automatic binning does not work, and I’m not sure how to provide a regex that automatically does binning for day, month, year options.

If I convert the type to Date in the Charts UI, the binning options appear, but the Chart does not load saying 'Invalid Date".

What is the best way forward to work with dates in Charts?

• Keep data as String, use regex for binning in charts?
• Keep data as String, convert to Date in Charts UI, … fix whatever is causing Invalid Date?
• Convert all data to Date type in db? (not sure how to make this automatic on new objects)
• Some other way
  List item

[steevej (Steeve Juneau)]

The real sensible action is

@Daniel_Nordh

Convert all data to Date type in db?

Dates as Date data type take less space than strings.
Dates as Date data type perform better than strings.
Dates as Date data type provide a rich date specific API without the need to convert every time.

@Daniel_Nordh

how to make this automatic on new objects

Hopefully you control the code that creates document. So it should be easy. Otherwise it should be
easy to write a change stream handler that automatically convert date string as date date.

[Daniel_Nordh]

I do control the creation of the objects, but I am not sure why they aren’t already saved as Date type, here is the node.js code for saving Date() with mongoose 5.13.15:

 dbo.collection("users").updateMany(
   {
     accountEmail: ...,
   },
   {
     $set: {
       ...some other parameter...,
       lastModified: Date(),
     },
   },
 );

At no point is it turned into String. Where could this be happening?

And would you mind sharing how to automatically turn any String dates into Date on the Atlas side?

[steevej (Steeve Juneau)]

You are sharing code that set the field lastModified but your screenshot shows that you are having issue with the field createdAt.

If you are not updating createdAt in your code then may be you are using mongoose or some other abstraction layer.

@Daniel_Nordh

And would you mind sharing how to automatically turn any String dates into Date on the Atlas side?

I have never done it so I cannot share anything. I just know that it should be possible

@steevej

to write a change stream handler

Here is a link to change stream.

[chris (Chris Dellaway)]

Date() returns a string, new Date() a Date object.

Date - JavaScript | MDN

As an exercise I converted the date string back to a date.

You could use this part as a project or for an update command etc… There probably is a better way to do it.

db.foo.find(
{},
{
  "createdAt": {
    "$toDate": {
      "$reduce": {
        "input": {
          "$slice": [
            {
              "$split": [
                "$createdAt",
                " "
              ]
            },
            6
          ]
        },
        "initialValue": "",
        "in": {
          "$concat": [
            "$$value",
            " ",
            "$$this"
          ]
        }
      }
    }
  }
}

[steevej (Steeve Juneau)]

Nice catch, I completely missed that.

Saru mo ki kara ochiru

[Daniel_Nordh]

Thanks to the tips above, here is my complete solution:

For new/updated objects
Use new Date() instead of Date()

For changing type of existing objects
I wrote this node function to update each user object.

function updateAllUserDates() {
  dbo
    .collection("users")
    .find()
    .forEach(function (obj) {
      obj.createdAt = new Date(obj.createdAt);
      dbo.collection("users").save(obj);
    });
}