Delete data from more than week ago don't delete data from last month

Working with Data Node.js Frameworks
, ,
1

Im creating a query to delete data more than seven days ago. But its not deleting data from previus month.
Example: I have data created at 02/03/2022 and 01/03/2022 and this data will be deleted, but the data with date 25/02/2002(month 02) will not.

Here is my code:

let selectedDay = moment().subtract(7, "d").toDate();
let remove = await mongoose.connection.db.collection("mycollection")
											.deleteMany({
												createdAt: {
													$lt: selectedDay
												}
											});

What Im doing wrong?

1 Like
2

Hi @Bruno_Cardoso,

I see you are using Node.js. You can do it like this:

let remove = await mongoose.connection.db.collection("mycollection").deleteMany({
  createdAt: {
    $lt: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000).toISOString() 
  }
});
3

Hi @NeNaD, thank you for reply. I try your suggestion but not work, I bealive the problem is the format of my date in db.

The format is:
image

So when I use .toISOString() the filter don’t work. Any suggestion?

4

Hi @Bruno_Cardoso,

I see you have your custom date format. In that case you can use _id property which is of type ObjectId. It has createdAt built in. You can try this query:

let remove = await mongoose.connection.db.collection("mycollection").deleteMany({
  _id: {
    $lt: ObjectId(
      Math.floor(new Date(Date.now() - 7 * 24 * 60 * 60 * 1000) / 1000).toString(16) + '0000000000000000'
    ),
  }
});
6

This not work. I change deleteMany to find to see the data:
image
(new Objectid is the console.log() of this query)

This query dont find any data. Same result to deleteMany. ;(

7

I tried this in my end and it works. How to you create new ObjectId? Can you try it with Mongoose?

const mongoose = require('mongoose');

let remove = await mongoose.connection.db.collection("mycollection").deleteMany({
  _id: {
    $lt: mongoose.Types.ObjectId(
      Math.floor(new Date(Date.now() - 7 * 24 * 60 * 60 * 1000) / 1000).toString(16) + '0000000000000000'
    ),
  }
});
8

Still not working… Your date in db is like?:
image

9

Hi @Bruno_Cardoso,

I updated my answer. You should not compare createdAt, but _id property. Can you try it again with this query:

const mongoose = require('mongoose');

let remove = await mongoose.connection.db.collection("mycollection").deleteMany({
  _id: {
    $lt: mongoose.Types.ObjectId(
      Math.floor(new Date(Date.now() - 7 * 24 * 60 * 60 * 1000) / 1000).toString(16) + '0000000000000000'
    ),
  }
});
10

The major issue is your model. You store your dates as a string rather than a date. And worst in a non ISO string format.

With your format you cannot sort or range query without parsing the string into its components.

You also deprive your self from the rich date API.

The only recommendation is to update your model and migrate your createdAt and updatedAt fields to real date data type.

11

Hi @NeNaD,

Here is my code:

image
image784×259 28.9 KB

Still not working. They don’t find any data with this query.

12

If you are going to use specific knowledge about an ObjectID internal structure please use the official API.

Also take a look at TTL indexes. You might have what you want right out of the box what you need. But I think your indexed fields need to be real date data type. Another reason to migrate.

13

I tried the same code in my end and it works. The only difference is that I am using defined schema models for my collections.

As @steevej suggested, you should change createdAt and updatedAt fields to be of type date and not of type string.

1 Like
16

I see… The problem is the type. But every time I format the type change to string. How can I insert date with format(“DD-MM-yyyy HH:mm.ss”) with format date?

17

You should not care about how the date is formatted in your stored data, it is probably some kind of number that takes less space than a string. The way it is displayed is ISO compliant. It simply needs to be the correct date type. If an ISO compliant format is not okay for you, you may display it in the format of your choice later at the presentation of your application.