Extract Time From Mongo ObjectID For Time Range Queries

  • Summarize 12 month trailing purchase records by user creation/signup time given that user ID’s are ObjectID’s
  • Summarize 12 month trailing activities by user session given that session ID’s are ObjectID’s

Anatomy of the ObjectID

Exclusively From MongoDB 3.2

5fee6600 | f75eabce97 | 882944
(1) (2) (3)
  1. The first 4 bytes (1) is the creation time in seconds since the Unix epoch
  2. The next 5 bytes (2) is the random value
  3. The last 3 bytes (3) is the incrementing counter

Inclusively To MongoDB 3.2

5fee6600 | f75eab | ce97 | 882944
(1) (2) (3) (4)
  1. The first 4 bytes (1) and the last 3 bytes (4) are the same as the later versions
  2. The second 3 bytes (2) is the machine identifier
  3. The next 2 bytes (3) is the process id

Extracting the Creation Time of the ObjectID’s

Hive

CAST(
CONV(
SUBSTRING(your_object_id, 0, 8),
16, 10
)
AS BIGINT)

Presto

FROM_BASE(
SUBSTR(your_object_id, 1, 8), -- index starts from 1
16
)

References

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Accessing Token and Rule Attributes

6 Things to Make you a Better Developer in 2021

What does the developer community has to say to the World !

Striking a More Specific Bargain with Records

images/aside-icons/info.png

My new app Fteam to help you to manage Celerities and team followup

Java Collections Framework

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
Injae Lee

Injae Lee

TODO

More from Medium

Efficient CPU-intensive GRPC service in Python — Part 2

Getting started with Kafka

Run a Spring boot docker image on windows with a MySQL local database connection

PostgreSQL Streaming Replication