How to Format the Date in MongoDB

If you have documents that store dates as Date objects, but you want to return them in a different format, you can use the $dateToString aggregate pipeline operator.

For example, you might want a date to be returned in mm/dd/yyyy format instead of the long ISODate() format that includes minutes, seconds, milliseconds, etc

The $dateToString operator converts the Date object to a string, and optionally allows you to specify a format for the resulting output.

Continue reading

How to Check Whether an Index is Hidden in MongoDB

From MongoDB 4.4, it’s possible to hide an index from the query planner. This allows you to evaluate the potential impact of dropping an index without actually dropping the index.

You can use the getIndexes() method to check whether or not an index is hidden. If an index is hidden, that index will display a hidden field as having a value of true (i.e. "hidden" : true).

Continue reading

How to Reference JSON Keys that Contain Special Characters when using OPENJSON, JSON_QUERY, and JSON_VALUE (SQL Server)

If you’re using a T-SQL function such as OPENJSON(), JSON_QUERY(), or JSON_VALUE(), you might be wary of any non-alphanumeric characters that might be in the JSON document that you’re working with. Especially if those special characters are in the key names, and you need to reference those key names.

Continue reading

How SUBDATE() Works in MariaDB

In MariaDB, SUBDATE() is a built-in date and time function that subtracts an amount from a given date.

It allows you to change a date by specifying the date, the unit to subtract, and the amount to subtract. You can pass a negative amount in order to add to the date, instead of subtracting from it.

SUBDATE() also has a shortcut syntax that allows you to specify the days to subtract.

Continue reading