2

Basically I have the same question as this one How to retrieve the date from a MongoDB ObjectId using SQL

That is, I know how to retrieve the timestamp associated to an ObjectId using pymongofor example, 

ObjectId('5d8dc6e0a26870000101c53d').generation_time
# datetime.datetime(2019, 9, 27, 8, 22, 56, tzinfo=<bson.tz_util.FixedOffset object at 0x000001C2A7F4F1C8>)

but I don't know how to do this with Snowflake. I have a column called _id containing the MongoDB ObjectIds.

How can I add a new field in my Snowflake table containing the timestamps associated to the ids?

fmarm
  • 4,209
  • 1
  • 17
  • 29

1 Answers1

2

My bad, I didn't get that a MongoDB ObjectId has the create timestamp embedded in the Id.

You can decode the timestamp with this:

SELECT TO_TIMESTAMP_NTZ(TO_NUMBER(SUBSTR(ObjectId, 1, 8), 'XXXXXXXX'))
FROM (SELECT '5d8dc6e0a26870000101c53d' ObjectId)

=> 2019-09-27 08:22:56.000

The id consists of the following parts:

  1. The first 4 bytes representing the seconds since the unix epoch
  2. The next 3 bytes are the machine identifier
  3. The next 2 bytes consists of process id
  4. The last 3 bytes are a random counter value

The list above (hinting about half the answer) taken from "How to convert from Timestamp to Mongo ObjectID"

My old wrong answer:

You can't retrieve a date from an object id. The reason why your Python code example works is that you have an entire object that has an id and other properties including generation_time.

If you just extract the id property from MongoDB then you don't have the other properties.

Hans Henrik Eriksen
  • 2,690
  • 5
  • 12