Converting MongoDB ObjectId to Timestamp in Snowflake: A Friendly Guide

Introduction

Hey there, fellow developers! Today, let's unravel a nifty trick that often stumps many of us when working with MongoDB and Snowflake.

Picture this: You've got data in MongoDB, each record stamped with that unique ObjectId MongoDB is famous for. Now, you need to migrate this data into Snowflake, but wait – how do you make sense of those timestamps hidden inside those ObjectIds? Fear not!

In this post, I'll walk you through a simple yet effective solution to extract those timestamps and bring them into the light in Snowflake.

---

The Challenge Explained

MongoDB's ObjectId isn't just a random jumble of characters; it's a treasure trove of information. The first 8 characters are actually a timestamp, representing when the data was created.

The catch? It's in hexadecimal format. And here's where Snowflake throws us a curveball – it doesn't have a built-in function to convert hexadecimal to decimal. So, how do we crack this code? Let's find out!

---

Step-by-Step Solution

Step 1: The Goal

• Objective: Extract the timestamp from MongoDB's ObjectId in Snowflake.
• The Hurdle: Snowflake's SQL doesn't directly convert hex to decimal.

---

Step 2: Our Secret Weapon – A Custom JavaScript UDF

• UDFs to the Rescue: In Snowflake, we can create User-Defined Functions (UDFs) to perform custom operations. We'll use JavaScript for our UDF.

• UDF Creation Script: Here's how we set up our UDF.
  

  CREATE OR REPLACE FUNCTION hex_to_dec(hex_str STRING)
  RETURNS FLOAT
  LANGUAGE JAVASCRIPT
  AS $$
      return parseInt(HEX_STR, 16);
  $$;
  

• What's Happening Here?: This snippet tells Snowflake, "Hey, let's turn those hex values into something we can work with in decimal!"

---

Step 3: Putting Our UDF to Work

• The Magic Query: Now, we use our UDF in a SQL query.
  

  CREATE OR REPLACE TABLE MONGO_IMPORT_WITH_TIMESTAMP AS
  SELECT
      *,
      TO_TIMESTAMP_NTZ(CAST(hex_to_dec(SUBSTR(_id, 1, 8)) AS INTEGER)) AS created_at
  FROM
      MONGO_IMPORT;
  

• Breaking It Down: This query is our secret sauce. It says, "Let's create a new table, MONGO_IMPORT_WITH_TIMESTAMP, where we'll turn those hex timestamps into human-friendly dates."

---

Step 4: Test and Celebrate

• Always Test: Before we break out the confetti, let's test this with some sample data.
• What to Expect: If all goes well, you'll have a shiny new table in Snowflake, with all your MongoDB timestamps now readable and ready to use.

---

Wrapping Up

In wrapping up, let's recall the words of W. Edwards Deming:

'Without data, you're just another person with an opinion.'

Our journey through converting MongoDB ObjectIds to readable timestamps in Snowflake is a testament to this truth. By unlocking the data hidden within these identifiers, we turn mere numbers into meaningful insights.

---

Helpful Resources

• Check out Snowflake's UDF documentation for more cool tricks.
• Stuck or want to share your success? Join the conversation in Snowflake forums.