Converting MongoDB ObjectId to Timestamp in Snowflake: A Friendly Guide
Tags: mongodb, snowflak, db, nosql
Published: 2023-11-24 14:02:27

### 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.


<hr>


### 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!

<hr>


### 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.

![Image depicts a data table with four columns and four rows. The first column header is '_id' indicating unique identifiers for records. The second column header is 'current_version' showing version numbers . The third column is 'model_id' . On the right side of the image, there is a  text 'CREATED_AT' with a shrugging emoji above it, suggesting the addition of a new 'created_at' column to indicate record creation times. The overall image conveys the process of enhancing a database with time-related data.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3o8fmw4cl22dklcg77mr.png)
<hr>
#### 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.

    ```sql
    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!"


<hr>


#### Step 3: Putting Our UDF to Work

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

    ```sql
    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."


<hr>


#### 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.


<hr>


### 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.


<hr>


### Helpful Resources

- Check out Snowflake's [UDF documentation](https://docs.snowflake.com/developer-guide/udf/javascript/udf-javascript-introduction) for more cool tricks.
- Stuck or want to share your success? Join the conversation in [Snowflake forums](https://community.snowflake.com/s/).