I need to create a TTL index, but the field was created as a string

I’ve been tasked with creating a TTL index on a logging table: delete after three days. However, the field they want me to use was created as a string, not a timestamp:

There are no good date fields to use in its place. The collection currently has 160M records, most of which will be deleted. I can see a few options, but none of them sound particularly appealing:

  1. Create a new timestamp field and use a trigger to update it based on the string field.
  2. Use $dateFromString in the TTL index definition.
  3. Extract the timestamp from the _id field and use that (probably fine).

Just looking for opinions on what will be the most performant, or if there’s an option 4 I’m not seeing. Thank you!

The only recommendation is to permanently convert your string dates as real Date values.

Any date values take less space than string equivalent, give better comparison performance and offer a richer API.

In your case, you may as well get rid of the items that would be removed by the TTL index as you run the conversion aggregation.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.