Unable to convert date string on Mongodb to Date with script

Question

I have this script to try to convert the current date as string Date: 'Thu Oct 19 2023 11:49:00 GMT+0200 (Central European Summer Time)', Into a Date format that mongoose can understand and sort

import config from 'config';

const url = config.get('mongoURI');
const dbname = config.get('dbname');
const db = `${url}${dbname}`;

mongoose.connect(db, {
  useNewUrlParser: true,
  useUnifiedTopology: true
});

const Tweet = mongoose.model('Tweet', {
  Date: Date, // Make sure your schema uses the Date data type for the Date field
  User: String,
  Tweet: String,
  URL: String
});

async function updateDates() {
  try {
    const tweetsToUpdate = await Tweet.find({});

    for (const tweet of tweetsToUpdate) {
      try {
        const currentDate = tweet.Date;
        const date = new Date(currentDate);

        if (!isNaN(date)) {
          tweet.Date = date;
          await tweet.save();
          console.log(`Updated Date for tweet ${tweet._id}`);
        } else {
          console.warn(`Skipped invalid date for tweet ${tweet._id}: ${currentDate}`);
        }
      } catch (error) {
        console.error(`Error updating tweet ${tweet._id}:`, error);
      }
    }

    console.log('Data migration completed.');
  } catch (error) {
    console.error('Data migration failed:', error);
  } finally {
    mongoose.disconnect();
  }
}

// Run the data migration script
updateDates();

It says all is good, but when I use this line on mongosh

db.tweets.aggregate([ { $limit: 1 }, { $project: { DateType: { $type: "$Date" } } } ])

I get:

[ { _id: ObjectId("6532e51c9dffaadae4a774ab"), DateType: 'string' } ]

So mongoose is unable to order the date using

await Tweets.find(query).sort({ Date: 1})

Does this answer your question? filtering by non-standard date string formats in MongoDB — ray
– ray, Commented Dec 5, 2023 at 15:33
Wow not really, it looks super complex, besides I do not have the same format. The question is more why my script does not work — Álvaro
– Álvaro, Commented Dec 5, 2023 at 15:36
When I tried to paste the whole aggregate on the terminal it keep failing, with loads of erros. — Álvaro
– Álvaro, Commented Dec 6, 2023 at 11:53

ray · Accepted Answer · 2023-12-05 21:26:41Z

Using the same idea in the duplicate link, you can break the raw date string into tokens and parse different date parts inside the tokens array. Use $dateFromParts to reconstruct the date object and use $merge to update back to the collection.

db.collection.aggregate([
  {
    "$set": {
      "tokens": {
        "$split": [
          "$Date",
          " "
        ]
      }
    }
  },
  // try to parse time part and break into hh, mm, ss
  {
    "$set": {
      "tokens": {
        "$reduce": {
          "input": "$tokens",
          "initialValue": [],
          "in": {
            "$cond": {
              "if": {
                $ne: [
                  -1,
                  {
                    "$indexOfCP": [
                      "$$this",
                      ":"
                    ]
                  }
                ]
              },
              "then": {
                "$concatArrays": [
                  "$$value",
                  {
                    "$split": [
                      "$$this",
                      ":"
                    ]
                  }
                ]
              },
              "else": {
                "$concatArrays": [
                  "$$value",
                  [
                    "$$this"
                  ]
                ]
              }
            }
          }
        }
      }
    }
  },
  // try to 1. parse month part and 2. convert into int
  {
    "$set": {
      "tokens": {
        $let: {
          vars: {
            tokens: "$tokens",
            monthArray: [
              "dummy",
              "Jan",
              "Feb",
              "Mar",
              "Apr",
              "May",
              "Jun",
              "Jul",
              "Aug",
              "Sep",
              "Oct",
              "Nov",
              "Dec"
            ]
          },
          in: {
            "$map": {
              "input": "$$tokens",
              "as": "t",
              "in": {
                "$switch": {
                  "branches": [
                    {
                      "case": {
                        "$in": [
                          "$$t",
                          "$$monthArray"
                        ]
                      },
                      "then": {
                        "$indexOfArray": [
                          "$$monthArray",
                          "$$t"
                        ]
                      }
                    }
                  ],
                  default: {
                    "$convert": {
                      "input": "$$t",
                      "to": "int",
                      "onError": "$$t"
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  {
    "$set": {
      "parsedDate": {
        "$dateFromParts": {
          "year": {
            "$arrayElemAt": [
              "$tokens",
              3
            ]
          },
          "month": {
            "$arrayElemAt": [
              "$tokens",
              1
            ]
          },
          "day": {
            "$arrayElemAt": [
              "$tokens",
              2
            ]
          },
          "hour": {
            "$arrayElemAt": [
              "$tokens",
              4
            ]
          },
          "minute": {
            "$arrayElemAt": [
              "$tokens",
              5
            ]
          },
          "second": {
            "$arrayElemAt": [
              "$tokens",
              6
            ]
          },
          "timezone": {
            "$replaceAll": {
              "input": {
                "$arrayElemAt": [
                  "$tokens",
                  7
                ]
              },
              "find": "GMT",
              "replacement": ""
            }
          }
        }
      }
    }
  },
  // cosmetics
  {
    "$project": {
      "Date": "$parsedDate"
    }
  },
  // update back to collection
  {
    "$merge": {
      "into": "collection",
      "on": "_id",
      "whenMatched": "merge"
    }
  }
])

Mongo Playground

I was not able to use the aggregate either by mongosh or with a script using mongoose, in the end I used plain JS to progressively first remove the at from the date to make it a proper date format and then converted to ISO
@Álvaro That is weird. Is there some restriction imposed from your DBA? Never heard of a shell that is unable to execute aggregate.

Collectives™ on Stack Overflow

Unable to convert date string on Mongodb to Date with script

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related