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

[Russell_Fox]

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!

[steevej (Steeve Juneau)]

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.