Converting MongoDB ObjectId to Timestamp

[Pranjal_m]

Hi,

I have obtained some data from mongo DB where instead of timestamp, we have object id stored in each row. Is there a way to convert this to timestamp in knime ?

Regards
Pranjal Mittal

[takbb]

Hi @Pranjal_m , I think it can probably be done from what I’ve read on the internet, but could you please upload some of these OIDs as actual text, so we don’t have to type them in and we can give it a try. thanks

[takbb]

update: I ported a javascript conversion from here:

MongoDB ObjectId to Timestamp Converter.

attached:
Mongodb OID to timestamp conversion.knwf (9.5 KB)

The first 8 characters are the timestamp in hexadecimal based on the unix epoch (seconds since 1 Jan 1970). So parsing that to an integer and then putting it into a UNIX Timestamp node should do the trick. I tried it with one of the samples from the uploaded picture, and it appears to give a “sensible” result

If anybody finds that it actually stores to greater granularity than “‘second’”, within the OID, then this answer could be adapted, but this is based on what I found so far

[Pranjal_m]

Thanks, this worked perfectly well