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

Cloud Computing and its features

The 10 Best Managed Software Testing Service Providers in 2020

Python Decorators: From Simple Decorators to Nesting Multiple

Data Structures: MULTI_SET vs SET vs UNORDERED_SET

Moving this site over to GitHub Pages

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

Change Data Capture with MongoDB Change Streams

PostgreSQL Database Set-Up on MacOS: PGAdmin and Postegreapp

Installing ODBC Driver for Remote Connection to PostgreSQL DB

Overview on Database Components