How to retrieve the date from a MongoDB ObjectId using SQL

Question

In MongoDB you can retrieve the date from an ObjectId using the getTimestamp() function. How can I retrieve the date from a MongoDB ObjectId using SQL (e.g., in the case where such an ObjectId is stored in a MySQL database)?

Example input:

507c7f79bcf86cd7994f6c0e

Wanted output:

2012-10-15T21:26:17Z

Answer 1

This can be achieved as follows (assuming objectId is a string) in MySQL:

SELECT FROM_UNIXTIME(
    CAST(CONV(SUBSTR(objectId, 1, 8), 16, 10) AS UNSIGNED)
) FROM table

It works as follows:

• SUBSTR(objectId, 1, 8) takes the first 8 characters from the hexadecimal objectId string
• CONV(..., 16, 10) converts the hexadecimal number into a decimal number and returns it as a string (which represents the UNIX timestamp)
• CAST (...) AS UNSIGNED converts the timestamp string to an unsigned integer
• FROM_UNIXTIME(...) converts the timestamp integer into the date

Note that by default the displayed date will be based on your system's timezone settings.