Extract Time From Mongo ObjectID For Time Range Queries

Anatomy of the ObjectID

5fee6600 | f75eabce97 | 882944
(1) (2) (3)
5fee6600 | f75eab | ce97 | 882944
(1) (2) (3) (4)

Extracting the Creation Time of the ObjectID’s

CAST(
CONV(
SUBSTRING(your_object_id, 0, 8),
16, 10
)
AS BIGINT)
FROM_BASE(
SUBSTR(your_object_id, 1, 8), -- index starts from 1
16
)

References

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store